Using “Text, To, Columns”

Topic

New Reply

Here’s one for the master loungers. I have a file of several hundred records which are text delimited with “double quotes” and the field delimiters are commas. A sample such as
“THE JUNKYARD, INC.”,”121 N TRASH ST”,”REFUSEHILLS KS 99999-0000″
Using the standard Text To Columns button in the menu guides you through the paces of selecting the field (double quotes) and column (comma) characters. All is well except when there is an embedded field character within the text delimiters such as in my example where the comma follows the word JUNKYARD. When this happens the “INC” appears in its own column instead of staying with the company name. I’ve searched TechNet without success and the options in Excel with no luck. Is there some option somewhere that turns on or off ignoring a column delimiter when embedded? I’m glad you guys are here to make up for either my ignorance or M$ ‘s forgetfulness.

Thanks

Reply | Quote

Replies

Here’s one for the master loungers. I have a file of several hundred records which are text delimited with “double quotes” and the field delimiters are commas. A sample such as
“THE JUNKYARD, INC.”,”121 N TRASH ST”,”REFUSEHILLS KS 99999-0000″
Using the standard Text To Columns button in the menu guides you through the paces of selecting the field (double quotes) and column (comma) characters. All is well except when there is an embedded field character within the text delimiters such as in my example where the comma follows the word JUNKYARD. When this happens the “INC” appears in its own column instead of staying with the company name. I’ve searched TechNet without success and the options in Excel with no luck. Is there some option somewhere that turns on or off ignoring a column delimiter when embedded? I’m glad you guys are here to make up for either my ignorance or M$ ‘s forgetfulness.

Thanks

You could change “,” for some other symbol and delimit the text on that instead.

Reply | Quote

Thanks, I have thought of this but this would be very impractical since our mail list processing system outputs this format for files that can range into the 100’s of thousands. I have tried this on several systems at work and even my at home one and it results in the same outcome. Surely this is not an individual anomaly and has occurred before. Any help from anyone will be appreciated.

Reply | Quote

Hi,

… text delimited with “double quotes” and the field delimiters are commas …

.

There is only 1 type of delimiter in your example data, which is the comma. The double quotes are a text qualifier. Any commas inside the pair of double quotes aren’t used to define a new field, so you would end up with:

[indent]THE JUNKYARD, INC. in the first column,
121 N TRASH ST in the second, and
REFUSEHILLS KS 99999-0000 in the third[/indent]

Reply | Quote

Hello Stephen,

I am aware that your response is how it should work, but for me it is not so simple and the results are just as I explained, why is that so? And how can I fix it?

Reply | Quote

Not sure what version you have, but does it look like the attached?

If the drop-down box on the right of the form has {none} or ‘ selected, the result is a you have experienced. If it is set to “ then it keeps THE JUNKYARD, INC together.

Reply | Quote

Stephen,

When I get to this point in the conversion the double quotes are selected but the “INC” appears in it’s own column. I am stymied. Attached is my sample.

Reply | Quote

Hello – Here is the result I get using what Stephen said. It looks correct to me.

Tim

PS: Why do you guys attach .pdf and .bmp files rather than .xls file?

Reply | Quote

You appear to have lost the opening double quote during the import…

Thus the first group is not considered “qualified” by the text qualifier…

Steve

Reply | Quote