+ Reply to Thread
Results 1 to 6 of 6

Decimal vs. Thousands Separator

  1. #1
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    349

    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
    Last edited by nsv; 12-28-2010 at 06:06 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #3
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    349

    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. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    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?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    349

    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. #6
    Forum Contributor
    Join Date
    08-22-2005
    Location
    Denmark
    MS-Off Ver
    Excel 2013
    Posts
    349

    Re: Decimal vs. Thousands Separator

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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