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?

Daleth

Do you still have the original CSV file? – Manvil

@Manvil Yes In the source it shows correctly. Just not while viewing. – Daleth

Can you give us a full sample line or two from your CSV? – Lumbard

possible duplicate of string (123) in Excel csv file – Bander

Yes, that is how it is converting it. However, if you look at the source it is formatted differently. – Daleth

Another possible duplicate: #308824 – Bander

I am exporting this using Navicat. Maybe someone knows how to make it stay as text during the export? – Daleth

There is no such thing as "make it stay as text during the export" because CSV inherently has no concept of numeric vs. text. How the data is interpreted is utterly and completely dependent on the thing that is reading the data, not on the CSV file itself. Your best bet is to use some kind of scripting language (Python, Perl, etc.) to process the raw CSV, before Excel gets its grubby little hands on it. – Bander

Oh hey, I just checked out Navicat's Web site and apparently there is an option to export directly to Excel format (rather than CSV). And this does have a chance at "making it stay as text during export". So try this! – Bander

Answer 1

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.

Punster

See also SuperUser – Minimalist

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?

Lumbard

Yes, I have. Yes, it works. Just to be anal, I just did it with that value. Everything is peachy. – Lumbard

Doesn't work, thanks though. The columns did not align correctly. – Daleth

What's not working properly? I literally just did this on my machine at work. I'll be happy to help out if I can. – Lumbard

Obviously not... that would produce 5 columns, because Excel doesn't know which commas are "real" delimiters and which are not. But that's beyond the scope of OP's question. – Lumbard

Seems like your idea would work but I can't get the columns aligned correctly. – Daleth

Hmmm... how are they not aligning correctly? Is it splitting up the numbers in that one column that you want to keep together? If so, you may be working with an improperly formatted CSV file. Any field in a CSV file containing a comma must be surrounded by quotes... this tells the computer "hey this quote is part of the data, not a delimiter!" We may be able to help with the alignment issue if you can post a line or two from the file. – Lumbard

That is what I was thinking but each column is double quoted and comma separated. EX: "510,1850", – Daleth

Ah... great. I see the problem. There are extra line breaks in the CSV and Excel treats an end of a line as an end of a row when you read in with the text import wizard... let me see if I can come up with a workaround. – Lumbard

Answer 3

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

Knowable

Recent versions of Excel are ignoring the ' or " and auto converting numeric strings to numeric fields. – Everywhere

Answer 4

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?

Lumbard

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.

Absa

Answer 6

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.

Patagonia

Answer 7

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.

Readability