Number format in CSV file

Question

I've got a macro that imports data from a SQL db to excel then saves the results as a CSV file. When it saves to CSV the formatting of two of the columns is lost, resulting in the numbers not displaying correctly.

Column 1 contains 12 digit numbers such as '800000000000' which are showing in the CSV file as '8E+11'

Column 2 contains 10 digit numbers such as '4880000000' which are showing in the CSV file as '4.88E+09'

The data displays fine when it is first imported to excel, the problem occurs when saving to CSV, although when I expand column 2 in the CSV file it displays the full number. This doesn't work for column 1 though, I have to change the formatting of the cell.

I usually run the macro then send the created CSV file on to a colleague, is there a way I can keep the number formatting without having to open and edit the CSV file?

Thanks

Answer 1

Adding "=" before the quotes preserves the pattern For Example if your csv file is like

A1,col1,col2,col3,="001"

The 5th column will still be 001 when opened in excel