+ Reply to Thread
Results 1 to 11 of 11

Removing Leading Zeros, maintaining zeros after decimal

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Removing Leading Zeros, maintaining zeros after decimal

    I have a list of numbers that I need to remove the leading zeros and still keep the "other" zeros.
    The column on the left is my original data, the one on the right is after using formatting, special .000, obviously this does not work.
    An example;
    0.71 .710 here I need to remove 0 prior to decimal without adding a 0 after the 1
    0.74 .740 here I need to remove 0 prior to decimal without adding a 0 after the 4
    0.863 .863
    0.87 .870 here I need to remove 0 prior to decimal without adding a 0 after the 7
    1.058 1.058
    1.451 1.451
    1.46 1.460 here I do not need to have a 0 after the 6
    1.503 1.503

    Thank you in advance for any help I can get on this matter.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Removing Leading Zeros, maintaining zeros after decimal

    If you'll never have more than three decimals, try Custom Format

    .###

    - Moo

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Removing Leading Zeros, maintaining zeros after decimal

    If you have a zero in your format string then a number (inclucdng a zero) will appear there, so you need to use # which is conditional (i.e. doesn't display zero if there is no number above or below). So, use a custom format of:

    #.0##

    Hope this helps.

    Pete

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Hi Moo,

    not seen you posting for a while. Hope you are well.

    Pete

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Removing Leading Zeros, maintaining zeros after decimal

    I would really like to see same sample answers, because, in places, your explanation is a bit confusing. But see if this will help?

    With your data in column A...
    =IF(LEFT(A1,1)="0",MID(A1,FIND(".",A1,1)+1,99),A1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Quote Originally Posted by Pete_UK View Post
    Hi Moo,

    not seen you posting for a while. Hope you are well.

    Pete
    likewise, good to have you back

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Hello Pete! I am doing well, thank you. I hope the same is true for you. Life gets in the way a bit more with young kids - so I can't spend hours at night online when I need to be sleeping. I do try to pop in once in a while to see how the party is raging on without me though. =)

    - Moo

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Thank you, as well, FD. Good to see you, too. =)

  9. #9
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Moo the Dog and Pete_UK - both of your suggestions worked great - Thank You. Also glad I could facilitate a reunion of sorts
    FDibbins your suggestion did remove the leading zero while maintaining the integrity after the decimal, but it also removed the decimal - Thank You, I appreciate the consideration.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Removing Leading Zeros, maintaining zeros after decimal

    If you want the decimal to remain, change to this...
    =IF(LEFT(A1,1)="0",MID(A1,FIND(".",A1,1),99),A1)

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Removing Leading Zeros, maintaining zeros after decimal

    Happy to help, thanks for the feedback

+ 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. Leading Zeros and decimal points
    By KL in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  2. [SOLVED] Leading Zeros and decimal points
    By KL in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  3. [SOLVED] Leading Zeros and decimal points
    By Sam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  4. Leading Zeros and decimal points
    By Sam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] Leading Zeros and decimal points
    By Sam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 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