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

If you can manipulate CVS file put ' in front of each number

Answer 2

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 3

OK... so, the file is using carriage return + line feed characters to delineate the beginning of a new record. It also (for reasons I don't understand) has line feed characters within each record at random places - but there are no carriage returns.

To fix this, I opened the file with Notepad++, and did a find and replace with "Extended" search mode. I replaced \n with nothing. The data now opens in Excel properly using my earlier recommended solution.

You can, of course, use any other program (not just Notepad++) to make this character substitution. Does that help?

Answer 4

The problem is about Excel thousands separator. My quick solution is simple and worked for me.

1. Go to Excel-->File-->Options-->Advanced
2. Find "Thousands separator". Probably your separator is ",".
3. Change the separator like "x", etc.

After you are done, I recommend to switch the separator as "," back.

Answer 5

Try this where DocNumber is actually text : Select (CHAR(10)+DocNumber) AS DocNumber

That is by adding an invisible text char it fools Excel into making it a Text string. You can use CHAR(32) too.

Answer 6

After trying random sets of chars I confirm that the method:

="003"

worked like a charm for any strange thing to put into those quotes: numbers, dates... It is the exact way to tell Excel that this value from CSV is a string. I needed that as CSV is easy to generate in a program, but user would open the file automatically via Excel.

Answer 7

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.