I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

Do you mean there are not commas separating the text? If you do have
commas, Text to columns would work using the comma as the delimiter.

If there are commas then when you chose Text to Columns, use the
Delimited option instead of Fixed and select the comma as the
delimiter.

If there are no commas use the quotation mark as the delimiter by
selecting the Other option and typing in a ". When you hit next, you
will have blank columns in between your desired text. Click on a blank
column in step three and chose the Do Not Import option. Do this for
each of the blank columns then click Finish.

Does that help?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529257

You have an active thread in .misc.


naulerich wrote:

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

--

Dave Peterson

Steve,

There are commas seperating the text but also throughout the data there for
I am unable to use as the delimiter. I will try using the quotes.

"SteveG" wrote:


Do you mean there are not commas separating the text? If you do have
commas, Text to columns would work using the comma as the delimiter.

If there are commas then when you chose Text to Columns, use the
Delimited option instead of Fixed and select the comma as the
delimiter.

If there are no commas use the quotation mark as the delimiter by
selecting the Other option and typing in a ". When you hit next, you
will have blank columns in between your desired text. Click on a blank
column in step three and chose the Do Not Import option. Do this for
each of the blank columns then click Finish.

Does that help?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529257

It is not working....I have changed the delimiter...removed the quotes...what
next?

"naulerich" wrote:

Steve,

There are commas seperating the text but also throughout the data there for
I am unable to use as the delimiter. I will try using the quotes.

"SteveG" wrote:


Do you mean there are not commas separating the text? If you do have
commas, Text to columns would work using the comma as the delimiter.

If there are commas then when you chose Text to Columns, use the
Delimited option instead of Fixed and select the comma as the
delimiter.

If there are no commas use the quotation mark as the delimiter by
selecting the Other option and typing in a ". When you hit next, you
will have blank columns in between your desired text. Click on a blank
column in step three and chose the Do Not Import option. Do this for
each of the blank columns then click Finish.

Does that help?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529257

naulerich wrote:

It is not working....I have changed the delimiter...removed the quotes...what
next?

"naulerich" wrote:


Steve,
--cut--

I'm not sure I understand the problem. I just took your data :

"123","test ,text","this is a test","abc"

1. Added a comma after the test and before text.
2. saved it as a txt and a csv file.
3. opened both file and the loaded as expected.

Ron

I am not sure of an easier way but once I imported, if I understand you
correctly, you want to remove all commas from between the text now. I
just highlighted the columns that had text with commas between it,
Ctrl-H to Find Replace. In the Find I put a "," and in Replace with a
space. Click Replace All. That will replace your commas with spaces
between your text. I tried the same as Ron suggested but still had
commas remaining between the text.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529257