+ Reply to Thread
Results 1 to 10 of 10

TextToColumns - Not consistently delimiting numbers

  1. #1
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,310

    TextToColumns - Not consistently delimiting numbers

    I'd appreciate any comments on the following. My Regional Setting is UK but from what I can see the situation is the same with a European setting (decimal point being a , and thousands separator a .)

    The attached workbook shows two records (a small subset of many more rows) with column labels in rows A1:A3

    I've taken a copy in A8:A10

    Note how when A8:A10 is selected and the TextToColumns functionality is used specifying the comma as a delimiter, with the output set to C8 there is an inconsistency between F9 & F10

    Both show the same characters, i.e. 21.38 and 148.065 but F9 is Text and F10 is a number and F10 displays as 148,065 and the underlying number is 148065

    The csv apparently comes from a USA source if that's relevant.
    The problem I'm trying to address is that the F10 value should be a decimal 148.065 not a whole number 148,065.
    I have a macro to remedy this but I'd like to understand why this happens in the first place.

    Something seems to identify and distinguish those records in the csv that have two numeric characters after the dot, from those that have three numeric characters after the dot so that all the two numeric records result as text and all the three numeric characters as numbers.

    Any ideas as to why this is?
    Ideally I'd like the TextToColumns to produce a consistent result, preferably both numbers, but at least both the same.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    8,947

    Re: TextToColumns - Not consistently delimiting numbers

    TextToColumns works perfectly for me. The only way I can replicate your result is to switch the decimal & thousands separator by select "Advanced" on page 3 of the Wizard.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,855

    Re: TextToColumns - Not consistently delimiting numbers

    Interesting, and I don't know if it is my US-centric settings, but I don't replicate the problem. If I select A1:A3 and execute a text to columns (delimited by comma and other settings as typical for my region), those numbers that are problematic in your example all show up as the correct numbers in mine.

    If I repeat the text to columns and at step 3 click on "advanced" and set "," as the decimal separator and "." as the thousands separator, then I replicate your result.

    I wonder if the text import wizard does not check your OS regional settings everytime it runs. Having run once with "." as thousands and "," as decimal separators, if it continues to use those settings unless/until you change it in the text import wizard. My brief test suggests that you will get the numbers to import correctly if you make sure that the text import wizard is using the correct decimal and thousands separators.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,310

    Re: TextToColumns - Not consistently delimiting numbers

    Thanks Mr S. Interesting.

    I had played around with the Advanced pop up to change the separator characters which made no difference.
    However, because you said it was working with a US Regional Setting I changed mine to US too.

    Now when I perform a new TextToColumns, magically both 'numbers' now appear as proper numbers. 21.38 and 148.065. So one step forward.
    It seems as though Excel seems to knows about where the csv came from (i.e. US) and remembers that but if so how? The subset of data was copied from the original US csv, so it's not as though the actual US csv was being used and there was some reference in the depths of the file that's not visible.

    However that seems to throw up another curious situation.
    Now with both as numbers, when I change the regional setting from US to a European setting say Denmark (decimal character a comma) and perform a new TextToColumns the numbers remain formatted the same. i.e. they don't change from 21.38 to 21,38.
    Is this perhaps suggesting that the TTC doesn't take any account of the regional settings when it does its stuff?

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,890

    Re: TextToColumns - Not consistently delimiting numbers

    How about Power Query
    Add ,"en-US" at changed type step to ensure that dot is used for decimal seperator.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,310

    Re: TextToColumns - Not consistently delimiting numbers

    Quote Originally Posted by Bo_Ry View Post
    How about Power Query
    Thanks,

    However I'm not sure that helps with the essential task which is to show the results in European format when used with Excel under European Settings.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,855

    Re: TextToColumns - Not consistently delimiting numbers

    When I change my OS regional settings, those changes are reflected in the parsed data (where the data were parsed as numbers), so I can't replicate that part of your test, either. If there is some kind of mysterious imprint that makes the file/text behave differently in other areas of the world, that may need to be tested by people in other areas of the world.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,310

    Re: TextToColumns - Not consistently delimiting numbers

    Quote Originally Posted by MrShorty View Post
    When I change my OS regional settings, those changes are reflected in the parsed data (where the data were parsed as numbers), so I can't replicate that part of your test, either. If there is some kind of mysterious imprint that makes the file/text behave differently in other areas of the world, that may need to be tested by people in other areas of the world.
    Thanks ,

    Just to confirm are you saying that if you change your OS settings to say Denmark, then starting with the csv which contains the embedded 21.38 if you then perform a TextToColumns, the result is 21,38, i,e what a Danish user would expect to see (meaning twenty eight point three eigh)t, i.e. the 38 appears as a decimal to a Danish user?

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,855

    Re: TextToColumns - Not consistently delimiting numbers

    Yes, that is correct. I see 21,38 in that cell, it is a number, and it means 21 and 38/100. I can't replicate the behavior you describe here.

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,890

    Re: TextToColumns - Not consistently delimiting numbers

    Add ,"en-US" at changed type step to ensure that dot is used for decimal seperator.
    This is for text before split to columns that uses dot as decimal seperator.
    01/07/21 02:41 PM EST,152.19,148.065,


    Quote Originally Posted by Richard Buttrey View Post
    Thanks,

    However I'm not sure that helps with the essential task which is to show the results in European format when used with Excel under European Settings.
    After splitting the real number is 148 and 065/1000.

    The display of the separator still depends on the region setting.

    A.png
    Last edited by Bo_Ry; 01-13-2021 at 11:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find which column's numbers are most consistently falling
    By Sarangsood in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-30-2019, 10:32 AM
  2. TextToColumns - Before the first delimiter .,.,.,.
    By bastord in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-11-2015, 08:52 AM
  3. Replies: 17
    Last Post: 03-26-2014, 12:57 AM
  4. Trying to do TextToColumns
    By gshock in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-05-2008, 04:10 PM
  5. TextToColumns > MDY
    By davehunter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2007, 04:44 AM
  6. TextToColumns
    By davehunter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2007, 05:35 AM
  7. Please assist with TextToColumns Thanks!!
    By jeffstryer@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2005, 08:06 AM

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