Another Excel question (exporting to CSV) - need help fast

Factual Questions
1

I need to save a file that has a column of numbers with leading zeroes (they are account numbers) as a CSV file, but the leading zeroes drop off. I have tried formatting the column as text first, but the leading zeroes still drop off.

A colleage suggested I add quotes around the account numbers in Excel before saving as CSV file, but I can’t figure out how to do that. The Concatenate function doesn’t seem to work with quotes.

Thanks in advance,
Roddy

2

Stick a ’ in front of the number. That will cause Excel to interpret it as text.

3

Format Cells->Number->Custom->Type in required number of zeros.

4

A suggestion on how to do that to 1600+ records, other than manually?

5

Thanks for the suggestion, but this did not work when exporting to CSV format.

6

The solution to this problem is to hate Microsoft a bit more.

7

Well, it works on Excel 2004 for OS X, sorry.

8

OK, never mind, it’s only when I re-open the CSV file in Excel that the leading zeroes are missing; if I open with WordPad they are there. That’s all I needed.
Roddy

9

If you moving data into Excel with leading zeroes that you want to keep, you should format the block of cells that might have leading zeroes as text before pasting that data into Excel. I don’t think that will help with opening a CSV file directly into Excel, but if WordPad keeps the leading zeroes, you could copy that data in WordPad then paste into Excel (using Paste Special, with with Paste Values).

10

Alternatively, rename the file to “.txt” instead of “.csv”. Then Excel will give you the import wizard where you can specify that the column is text instead of numeric; change it to TEXT type to retain the leading zeroes.

Or use Import External Data from the Data menu in Excel to get the wizard, instead of renaming the file.

(I swear there used to be one way to open CSV files and get the wizard, but they “fixed” that in O2003.)