Stop Excel from converting a string to a number
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
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?