+ Reply to Thread
Results 1 to 6 of 6

Mysterious Spacing Turn Number Into Text With Leading Zero

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Mysterious Spacing Turn Number Into Text With Leading Zero

    I need to create an excel list from the online database so that what I did is just copy and paste. The online database listing start with a check box, then a number, then description. And what surprise me is that the code with leading zero usually won't appear but now it did. I try to figure it out and all I know is that there are just two spacing a the beginning of the number. I have attached the sample excel sheet below for reference. Is there anyone can explain this to me?
    Attached Files Attached Files
    Last edited by jackgan; 07-31-2013 at 09:11 AM.

  2. #2
    Registered User
    Join Date
    07-02-2013
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Mysterious Spacing Turn Number Into Text With Leading Zero

    Hi,

    This can happen if you import data from another source.

    If the numbers are in a separate column, you can use Text-to-Columns and that should turn it into a number.


    Regards,
    Hennie

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Mysterious Spacing Turn Number Into Text With Leading Zero

    Hennie, did you try that suggestion on the attached sample workbook? The data has hidden characters (code=160) that are not standard spaces (code=32).

    My suggestion:

    1) In B1: =MID(A1,3,LEN(A1))+0
    2) Copy B1 down as needed
    3) Copy column B and Paste Special > Values over column A.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    07-02-2013
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Mysterious Spacing Turn Number Into Text With Leading Zero

    I did try it. Just highlight the column, text-to-columns, select delimited, delimiters should be Space and Finish.

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: Mysterious Spacing Turn Number Into Text With Leading Zero

    Thanks for the reply and now I know that it is hidden characters instead of regular space. And both method from you two did turn that into number. Thank you guys so much!!!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: (Solved) Mysterious Spacing Turn Number Into Text With Leading Zero

    I've properly marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

+ 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. Getting the text value of a number with a leading zero
    By Dr.John in forum Excel General
    Replies: 2
    Last Post: 08-20-2010, 09:23 AM
  2. Converting text to number, dropping leading zero
    By MichelleW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2009, 04:31 PM
  3. [SOLVED] leading zeros when uploading a number from text file
    By mc01234 in forum Excel General
    Replies: 1
    Last Post: 05-18-2006, 03:55 PM
  4. [SOLVED] How to copy a number into a text cell, keeping leading zeros?
    By Basher Bates in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-15-2006, 08:10 AM
  5. zero supress leading zeros when chg format from text to number
    By HeatherO in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2005, 08:06 PM

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