Stop Excel from converting a string to a number

MagentoMan picture MagentoMan · Jan 3, 2014 · Viewed 12.5k times · Source

I have a column in my CSV file that contains a string of numbers separated by commas. Excel keeps converting them to numbers even though I want to treat it as text.

Example:

470,1680 get converted to 4,701,680

However, I want it to stay as 470,1680

I tried to format the cells as text but that removes the original comma. How can I achieve this?

Answer

John Chrysostom picture John Chrysostom · Jan 3, 2014

Rename the .CSV file to a .TXT file. Open the file with Excel, and the text import wizard will pop up. Tell Excel that it's a delimited file and that a comma is the delimiter. Excel will then give you a screen that allows you to assign formats to each column. Select the text format for the column in question. Import and you're done!

To test this, I created the following .CSV file:

test1,"470,1680",does it work
test2,"120,3204",i don't know

When opening the CSV directly in Excel, I get the following:

test1    4,701,680    does it work
test2    1,203,204    i don't know

When opening using my method, I get this instead:

test1    470,1680    does it work
test2    120,3204    i don't know

Is this not the desired result?