Stop Excel from converting a string to a number

Question

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 1

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?

Answer 2

What I found that worked was this:

="12345678901349539725", "CSV value2", "Another value"

The key here is that this value is a string containing ="{Number}". Somehow, Excel respects that pattern.

Perhaps it could be better written as

"="12345678901349539725"" 

But don't go crazy with the quotes in your code.