Page 1 of 2 12 LastLast
Results 1 to 10 of 15

How to remove leading zeros in Excel

This is a discussion on How to remove leading zeros in Excel within the Excel Questions forums, part of the Question Forums category; I have a lot of record that contain number like shown below: 0000082181 0000005465 0000028997 I want to remove all ...

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    5

    Talking How to remove leading zeros in Excel

    I have a lot of record that contain number like shown below:
    0000082181
    0000005465
    0000028997

    I want to remove all 0 in front of this number in excel. Can anyone here
    help me.. I use excel 2007..

    I already try using formula


    Code:
    =IF(     LEFT(A1) = "0" ,     RIGHT(A1, LEN(A1)-5),     A1)
    but it only remove 5 character in front what about the number that have 6 '0'......
    Please help..

    Thanks in advance.


  2. #2
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

    Default Re: How to remove leading zeros in Excel

    Hi akuccputsedut
    and welcome to the board.
    Look here:

    Remove leading zeros
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    5

    Talking Re: How to remove leading zeros in Excel

    Thanks Robert Mika.. I have read that thread.. not one of them resolved my problem...

    maybe have any idea... thanks..

  4. #4
    Board Regular
    Join Date
    Sep 2010
    Location
    Sunnyvale, CA. USA
    Posts
    526

    Default Re: How to remove leading zeros in Excel

    The example Robert gave should work in post #4 in his link:
    Remove leading zeros

    It does appear that they are text instead of numeric. So perhaps try this:

    In cell A1 copy in one of your numbers like 0000082181
    In cell B1 just enter in the number 1
    In cell C1 type in the formula =A1*B1 and push enter

    Your cell C1 should now be the number 82181 without the leading 0's

    You could then format columns B and C to do as many as you have in column A.

    And welcome to the board!
    Last edited by chuckchuckit; May 6th, 2011 at 01:34 AM.
    Patience and perseverance are qualities of enduring. And with help from God and through others, are where answers can be found.

    ==== . -..- -.-. . .-.. =====
    I use Excel 2007, Windows 7
    == ..--- ----- ----- --... ==

  5. #5
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

    Default Re: How to remove leading zeros in Excel

    Try to copy those numbers to Notepad and then back to your spreadsheet.
    I copied your numbers from here straight to my spreadsheet and they came without zeros.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  6. #6
    New Member
    Join Date
    May 2011
    Posts
    5

    Talking Re: How to remove leading zeros in Excel

    Thanks chuckchuckit I already test the #4 but it's not work..no effect

    when I tried your solution step by step.. it giving me error "a value used in the formula is of the wrong data type"

    I used TEXT for my data and GENERAL for other column...

    appreciate ur help... thanks..


    Quote Originally Posted by Robert Mika View Post
    Try to copy those numbers to Notepad and then back to your spreadsheet.
    I copied your numbers from here straight to my spreadsheet and they came without zeros.
    I have more than 80k records.. so it quite difficult to copy all in notepad and copy back in spreadsheet...
    Last edited by akuccputsedut; May 6th, 2011 at 01:40 AM.

  7. #7
    Board Regular
    Join Date
    Sep 2010
    Location
    Sunnyvale, CA. USA
    Posts
    526

    Default Re: How to remove leading zeros in Excel

    It should work no problem with General formatting. But try Numeric formatting for columns B and C and see if that might do it?

    Also am wondering where you are getting your numbers with the leading zero's from? Copying, importing etc?
    Patience and perseverance are qualities of enduring. And with help from God and through others, are where answers can be found.

    ==== . -..- -.-. . .-.. =====
    I use Excel 2007, Windows 7
    == ..--- ----- ----- --... ==

  8. #8
    Board Regular
    Join Date
    Sep 2010
    Location
    Sunnyvale, CA. USA
    Posts
    526

    Default Re: How to remove leading zeros in Excel

    I once ran into something like this where I was not able to use any data at all from a source. And after a lot of experimenting I found that they had hidden a leading character that you somehow could not see.

    So once I erased the leading character, then the data was usable. I will try to look up that code I used to do that.
    Patience and perseverance are qualities of enduring. And with help from God and through others, are where answers can be found.

    ==== . -..- -.-. . .-.. =====
    I use Excel 2007, Windows 7
    == ..--- ----- ----- --... ==

  9. #9
    New Member
    Join Date
    May 2011
    Posts
    5

    Talking Re: How to remove leading zeros in Excel

    Quote Originally Posted by chuckchuckit View Post
    It should work no problem with General formatting. But try Numeric formatting for columns B and C and see if that might do it?

    Also am wondering where you are getting your numbers with the leading zero's from? Copying, importing etc?
    the data was import from database...

  10. #10
    Board Regular
    Join Date
    Sep 2010
    Location
    Sunnyvale, CA. USA
    Posts
    526

    Default Re: How to remove leading zeros in Excel

    There may be different ways to import that data. Try some different settings. Perhaps first see what the import setting is that you are currently using. It seems it may be text. Try to change it to numeric importing if you can. That might fix it right there?
    Patience and perseverance are qualities of enduring. And with help from God and through others, are where answers can be found.

    ==== . -..- -.-. . .-.. =====
    I use Excel 2007, Windows 7
    == ..--- ----- ----- --... ==

Page 1 of 2 12 LastLast

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
  •  


DMCA.com