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?
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.
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?
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?
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.
The problem is about Excel thousands separator. My quick solution is simple and worked for me.
After you are done, I recommend to switch the separator as "," back.
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.
