How to keep EXCEL 2003 from messing up comma-delimited text imports.

EXEL 2003 works a little differently than previous versions when you import comma delimited numbers. The folks at Microsoft probably thought this was a wonderful improvement. If a row can be interpreted as one number, i.e. there are three digits set off to the right of the comma, then the row is interpreted as one number, the comma is eliminated, but the cell into which it is imported is set to number format with commas.

Data below was copied and pasted into EXCEL from the webpage shown.Note the entry in cell A13, and the the number behind it in the formula bar. Numbers, by default are aligned to the right. Rows that were correctly identified as text are aligned to the left. Attempts to parse these data to two columns using Data/text to columns (comma delimiter) fail to parse the cells that had the comma removed.

Putting in the original data with Paste special/ text (column C) didnít avoid this problem.

The workaround for this problem is to set the comma delimiter before pasting in the original data.

You can do that by entering the text to column dialogue,setting the comma delimiter, then cancel out of it, With the comma delimiter set beforehand, when you paste in the data from the webpage, it automatically pastes and parses in one step. The result of paste (with the comma delimiter set ahead of time) is shown in columns G and H. Just to confirm this was the cause of the pasting /parsing, I removed the comma-delimited specification from text to columns and pasted in the original data into column J and obtained the original result.
It's also necessary to paste special/ text.
This automatic parsing may cause a problem if you are pasting numbers that you donít want parsed, but which have commas in them in the original source.So if you are getting unwanted results from pasting data into EXCEL 2003, adjusting specifications in the Text to columns (import) Wizard may help. Another option is to replace the commas in the original source data with a different delimiter

Return to BA3300 homepage