Is there a way to remove quotation marks from an Excel spreadsheet?

My file has looks like this:

"000151","001","SCREW-5/16-18 X 5/8 HEX CAP","A1"
"000922","001","SCREW-10-32X1/4 RHM-ZP"

But everything I try it I either get it to import but I loose the leading
00's (zero's) or it looks like this.

"000151" "001" "SCREW-5/16-18 X 5/8 HEX CAP" "A1"
"000922" "001" "SCREW-10-32X1/4 RHM-ZP" "A1"
"001085" "001" "NUT-3/8-16 HEX" "A1"

How do I get to import and get to look like this?

000151 001 SCREW-5/16-18 X 5/8 HEX CAP A1

I am using the Wizard in the Excel program.

Format as Text and edit>replace " with nothing
or record a macro and clean it up as shown in fixtext

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/5/2008 by Donald B. Guillett
'

'
Columns("A:A").Select
Selection.NumberFormat = "@"
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Sub fixtext()
With Columns("A")
.NumberFormat = "@"
.Replace """", ""
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Jeffrey" <Jeffrey@discussions.microsoft.com> wrote in message
news:005AF96A-F4F0-4BA6-A3CA-0E284A0986D2@microsoft.com...
> Is there a way to remove quotation marks from an Excel spreadsheet?
>
> My file has looks like this:
>
> "000151","001","SCREW-5/16-18 X 5/8 HEX CAP","A1"
> "000922","001","SCREW-10-32X1/4 RHM-ZP"
>
> But everything I try it I either get it to import but I loose the leading
> 00's (zero's) or it looks like this.
>
> "000151" "001" "SCREW-5/16-18 X 5/8 HEX CAP" "A1"
> "000922" "001" "SCREW-10-32X1/4 RHM-ZP" "A1"
> "001085" "001" "NUT-3/8-16 HEX" "A1"
>
> How do I get to import and get to look like this?
>
> 000151 001 SCREW-5/16-18 X 5/8 HEX CAP A1
>
> I am using the Wizard in the Excel program.
>
>
>

I created a test file (plain old text) with your two lines of data in it.

I called it: C:\test.txt

I opened excel
I did File|Open|c:\test.txt
I was shown the text import wizard and I chose:
Delmited by comma

But I also told excel that each field should be treated as text (not General,
not a date, not skipped)

I got text values with leading 0's in my worksheet.



Jeffrey wrote:
>
> Is there a way to remove quotation marks from an Excel spreadsheet?
>
> My file has looks like this:
>
> "000151","001","SCREW-5/16-18 X 5/8 HEX CAP","A1"
> "000922","001","SCREW-10-32X1/4 RHM-ZP"
>
> But everything I try it I either get it to import but I loose the leading
> 00's (zero's) or it looks like this.
>
> "000151" "001" "SCREW-5/16-18 X 5/8 HEX CAP" "A1"
> "000922" "001" "SCREW-10-32X1/4 RHM-ZP" "A1"
> "001085" "001" "NUT-3/8-16 HEX" "A1"
>
> How do I get to import and get to look like this?
>
> 000151 001 SCREW-5/16-18 X 5/8 HEX CAP A1
>
> I am using the Wizard in the Excel program.

--

Dave Peterson

Thanks that worked for me.
One more thing.
When I do this I get an error box, yellow triangle with a ! in it.
There are a number of items you can chose from, such as, number stored as
text or convert toa number, etc.

All of the numbers in the columns 1 and 2 has this error. How do I get rid
of these errors.

"Dave Peterson" wrote:

> I created a test file (plain old text) with your two lines of data in it.
>
> I called it: C:\test.txt
>
> I opened excel
> I did File|Open|c:\test.txt
> I was shown the text import wizard and I chose:
> Delmited by comma
>
> But I also told excel that each field should be treated as text (not General,
> not a date, not skipped)
>
> I got text values with leading 0's in my worksheet.
>
>
>
> Jeffrey wrote:
> >
> > Is there a way to remove quotation marks from an Excel spreadsheet?
> >
> > My file has looks like this:
> >
> > "000151","001","SCREW-5/16-18 X 5/8 HEX CAP","A1"
> > "000922","001","SCREW-10-32X1/4 RHM-ZP"
> >
> > But everything I try it I either get it to import but I loose the leading
> > 00's (zero's) or it looks like this.
> >
> > "000151" "001" "SCREW-5/16-18 X 5/8 HEX CAP" "A1"
> > "000922" "001" "SCREW-10-32X1/4 RHM-ZP" "A1"
> > "001085" "001" "NUT-3/8-16 HEX" "A1"
> >
> > How do I get to import and get to look like this?
> >
> > 000151 001 SCREW-5/16-18 X 5/8 HEX CAP A1
> >
> > I am using the Wizard in the Excel program.
>
> --
>
> Dave Peterson
>

Never mind I found the solution.

"Jeffrey" wrote:

> Thanks that worked for me.
> One more thing.
> When I do this I get an error box, yellow triangle with a ! in it.
> There are a number of items you can chose from, such as, number stored as
> text or convert toa number, etc.
>
> All of the numbers in the columns 1 and 2 has this error. How do I get rid
> of these errors.
>
> "Dave Peterson" wrote:
>
> > I created a test file (plain old text) with your two lines of data in it.
> >
> > I called it: C:\test.txt
> >
> > I opened excel
> > I did File|Open|c:\test.txt
> > I was shown the text import wizard and I chose:
> > Delmited by comma
> >
> > But I also told excel that each field should be treated as text (not General,
> > not a date, not skipped)
> >
> > I got text values with leading 0's in my worksheet.
> >
> >
> >
> > Jeffrey wrote:
> > >
> > > Is there a way to remove quotation marks from an Excel spreadsheet?
> > >
> > > My file has looks like this:
> > >
> > > "000151","001","SCREW-5/16-18 X 5/8 HEX CAP","A1"
> > > "000922","001","SCREW-10-32X1/4 RHM-ZP"
> > >
> > > But everything I try it I either get it to import but I loose the leading
> > > 00's (zero's) or it looks like this.
> > >
> > > "000151" "001" "SCREW-5/16-18 X 5/8 HEX CAP" "A1"
> > > "000922" "001" "SCREW-10-32X1/4 RHM-ZP" "A1"
> > > "001085" "001" "NUT-3/8-16 HEX" "A1"
> > >
> > > How do I get to import and get to look like this?
> > >
> > > 000151 001 SCREW-5/16-18 X 5/8 HEX CAP A1
> > >
> > > I am using the Wizard in the Excel program.
> >
> > --
> >
> > Dave Peterson
> >