Decimal vs. Thousands Separator

**nsv** · 12-22-2010, 04:52 AM

I import an array of data from a text document to Excel; the array contains only numbers.
The problem is that if a number has exactly three digits after the decimal the decimal separator is considered to be a thousands separator; thus 1.446 (one point four four six) becomes 1446 (one thousand four hundred forty six.

Control panels regional and language options is set to decimal comma and point for thousands separator and that is fixed as it is a company owned computer with only IT-dept. access to such things. However, Excel operates with decimal point. I don't know this is set up.

NSV

**DonkeyOte** · 12-22-2010, 05:11 AM

It's not clear how you're importing the text document but if you're going through the Import Wizard you should find that in Step 3 there is an "Advanced" button - if you click that you can specify which delimiters to use.

**nsv** · 12-22-2010, 09:01 AM

I just use CTRL+C in the text document (a table in Word) followed by CTRL+V in Excel.
It works perfectly except for those specially designed numbers.

**ChemistB** · 12-22-2010, 10:07 AM

How about before you copy, do a find and replace in Word and switch out the "."? Would that work for you?

**nsv** · 12-22-2010, 04:34 PM

I already have to do that, as all the numbers arrive from a source that uses decimal comma, so when I paste them into Excel there is only decimal points and no commas.
Numbers containing commas are considered text, but even "find and replace" commas with points does not solve the problem - regardless whether this is done directly in Excel or beforehand in Word or another text editor.

Apparently some setup in Excel prompts the program to interpret numbers, that end with a point followed by three digits, as if this point is a thousands separator.

A solution would be to add manually an extra 0 to all the numbers ending with three digits after the decimal, but that is not an option if you have 2000 lines.