strip off leading zeros
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: strip off leading zeros

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Detroit area
    Posts
    995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default strip off leading zeros

    00080236528-GP0KA

    I need the leading zeros to go away.
    leaving me with
    80236528-GP0KA

    thanks
    Note, there are varying numbers of 0s' and the length of the string isnt the same all the time.
    Last edited by Jeffrey Green; Jun 20th, 2018 at 10:51 AM. Reason: clarity

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,417
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: strip off leading zeros

    Will the entries always be all numeric values, followed by a dash, followed by a string?, i.e.
    number-string
    Last edited by Joe4; Jun 20th, 2018 at 11:01 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,318
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: strip off leading zeros

    AB
    100080236528-GP0KA80236528-GP0KA

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=SUBSTITUTE(TRIM(SUBSTITUTE($A1,"0"," "))," ","0")



    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,095
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: strip off leading zeros

    =left(a1,find("-",a1)-1)+0&right(a1,len(a1)-find("-",a1)+1)

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,417
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: strip off leading zeros

    If my initial assumption is correct (in my first reply), here is the formula I came up with:
    Code:
    =VALUE(LEFT(A1, FIND("-",A1)-1)) & MID(A1,FIND("-",A1),LEN(A1))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,126
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: strip off leading zeros

    @ wideboydixon:

    With your solution, 00080230008-GP0KA --> 802308-GP0KA

  7. #7
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,126
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: strip off leading zeros

    Here is my take on it -- removes up to 9 leading zeros:

    =MID(A1,1+COUNT(FIND(CHAR(1)&REPT(0,{1,2,3,4,5,6,7,8,9}),CHAR(1)&A1)),99)

  8. #8
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,095
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: strip off leading zeros

    Quote Originally Posted by Tetra201 View Post
    @ wideboydixon:

    With your solution, 00080230008-GP0KA --> 802308-GP0KA
    Darn I liked that solution.
    Last edited by Special-K99; Jun 20th, 2018 at 12:35 PM.

  9. #9
    Board Regular
    Join Date
    Mar 2018
    Posts
    161
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: strip off leading zeros

    Quote Originally Posted by wideboydixon View Post
    A B
    1 00080236528-GP0KA 80236528-GP0KA
    Sheet1

    Worksheet Formulas
    Cell Formula
    B1 =SUBSTITUTE(TRIM(SUBSTITUTE($A1,"0"," "))," ","0")



    WBD

    What about 0's to the right of the string? u remove them as well with trim!!

    u can add IF to the end... would this be the way u would do it?

    =SUBSTITUTE(TRIM(SUBSTITUTE($A1,"0"," "))," ","0")&IF(RIGHT($A1,1)="0","0","")
    MS Excel wasn't called "Microsoft Perfect" (although it should have been...)
    Because you can never know everything about it...
    but you can always "Excel" more!

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,464
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: strip off leading zeros

    Hi,

    How about:

    AB
    100080236528-GP0KA80236528-GP0KA
    200080230008-GP0KA80230008-GP0KA
    300080036500-GP0KA80036500-GP0KA

    Sheet94



    Worksheet Formulas
    CellFormula
    B1=LEFT(A1,FIND("-",A1)-1)+0&MID(A1,FIND("-",A1),255)


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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