Decimal vs. Thousands Separator

1. Decimal vs. Thousands Separator

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

2. Re: Decimal vs. Thousands Separator

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.

3. Re: Decimal vs. Thousands Separator

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.

4. Re: Decimal vs. Thousands Separator

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

5. Re: Decimal vs. Thousands Separator

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.

6. Re: Decimal vs. Thousands Separator

Well, I found it at last.
Tools, Options, International, Number handling: Thousands separator must be either space or comma

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1