problem converting delimited text to Excel

[Mukesh Thakkar]

I have a text file with these two lines
"37-3'3"","93-23'30"",
"39-9'39"N","94-34'49"W",

(This is latitude, longitude information)
When I open the file in Excel and on the wizard I click
comma as the delimiter and " as the text qualifier the
first row is put into one column only while the second
row is put into two columns (which is the right thing).
How can I solve this problem?
Thanks

 

[Guest]

If you set the text qualifier as {None} it appears to do the job.

Or you could do a global replace of the string "" in your text file. Replace it with "V" (ie some value that is not N.S,E or W) then import your text file into excel and use replace in Excel to replace the V with nothing.

unlikeKansas

 

[AlfD]

Hi!

The combination of text qualifier ={none} and delimiter = comma works.

Al

 

[hgrove]

unlikeKansas wrote...

If you set the text qualifier as {None} it appears to do the job.

Or you could do a global replace of the string "" in your text file.
Replace it with "V" (ie some value that is not N.S,E or W) then
import your text file into excel and use replace in Excel to
replace the V with nothing.

...

Tweaking this, if the OP imports the file without parsing into fields
then replaces all "" (2 in sequence) with """ (3 in sequence), s/h
could then use Data > Text to Columns, Delimited, using comma a
delimiter *and* " as text qualifier, and Excel will parse into field
as desired