I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros:


I'd like to remove the leading zeros so that the values become:


How would I do this in an Excel formula? I'd prefer to avoid use of a VBA macro.

  • If you have a few more contraints that are on the data, I may be able to come up with a more specific solution. Sep 17 '09 at 19:40
  • Thanks. The values have leading zeroes so that they're 9 characters total. Other than that, the values are strings of digits 0-9 mixed with capital letter A-Z. It is possible that a trailing zero exists and it is part of the actual value and shouldn't be trimmed. Sep 17 '09 at 19:50
  • To be explicit, there are never embedded spaces. Just 0-9 and A-Z. Sep 17 '09 at 19:58
  • Edited to include those trailing zero examples Sep 17 '09 at 20:01
  • @Chris, I can do it if we know the max number of zeroes that will ever appear embedded in the string, or trailing after (though it will be a long formula). Sep 17 '09 at 22:19

Here's a solution that's cell-intensive but correct.

Put your data in column A.

In B1, put the formula:

    LEFT(A1) = "0" ,
    RIGHT(A1, LEN(A1)-1),

This checks for a single leading zero and strips it out.

Copy this formula to the right as many columns as there can be characters in your data (9, in this case, so you'll be going out to column J). Copy it down for each row of data.

The last column contains your data, stripped of leading zeros.

  • After many attempts at this, this is the only version which will work without resorting to VBA.
    – skamradt
    Sep 17 '09 at 21:36
  • Comment by @Richard: I like Isaac's solution. If more than one leading zero, keep copying from cells containing his formula and pasting values only to the original cells: each time will strip another leading zero (if any).
    – Ivo Flipse
    Jan 13 '11 at 16:20

The following formula neither needs additional cells nor does it have to be entered as an array formula:

=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1)

If a single zero shall be returned for strings like 0 or 00, the following formula may be used:

=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ","0",""),1),LEFT(A1,LEN(A1)-1)&" ")+1))
  • This answer works with multiple leading zero's and seems to be the most complete and simple of the answers mentioned here.
    – Peter
    Aug 17 '15 at 7:15
  • A detailed explanation of how this formula works was given @ trumpexcel site that I ran into while looking for a solution to this problem. The solution there assumes there aren't any "all-zeros" value apparently. @Andreas's solution above adds spaces to fix that possibility it seems.
    – OzgurH
    Feb 4 '21 at 8:38

This is a tough problem to do with a worksheet function. The following will do the trick, but only if the zeros that aren't leading come only one at a time, and there are no trailing zeroes, and no embedded spaces.

It substitutes all zeros with spaces, trims them (all but embedded single spaces), then substitutes back the zeroes.

  • 1
    Why only if the zeros that aren't leading come only one at a time? And what about =CLEAN(SUBSTITUTE(TRIM(SUBSTITUTE(A1 & CHAR(9),"0"," "))," ","0")) (hoping CLEAN will remove Tabs; if not; use some other value in CHAR)
    – Arjan
    Sep 17 '09 at 19:52
  • @Arjan, I tested TRIM and it does replaced embedded multiple spaces with a single space, but I'm going to research your info now and see what I can find. (I'm using 2003). Sep 17 '09 at 20:15
  • The CLEAN function saves the embedded spaces, but also saves all but the first leading space. Sep 17 '09 at 20:21
  • I added CLEAN to remove the CHAR(9) I added; using CLEAN on the TRIMmed value should not have leading spaces...?
    – Arjan
    Sep 17 '09 at 20:23
  • Yes, but when the CHAR(9) was added (to each zero), it added itself to the leading zeroes also. I see that you only used it to save the trailing zeroes and I'll incorporate it into the answer. Sep 17 '09 at 20:41

This solution works for input values that are all exactly nine characters.

First set up a fixed ten row table that contains the following numbers formatted as text.











Lets say the table is in cells A1 thru' A10

lets say your input value is in cell B1 and your result cell is C1

Use the following formula in cell C1


  • Smart! As for the third parameter in the MATCH function: 1 = find the largest value less than or equal to lookup_value
    – Arjan
    Sep 18 '09 at 7:09

For data in A1, use the following formula:

=RIGHT(A1,LEN(A1)-MAX((FINDB(REPT("0",ROW(A$1:A$100)),A1 & "-" & REPT("0",100))=1)*ROW(A$1:A$100)))

inputing by Ctrl+Shift+Enter. It does work for strings up to 100 characters.

  • Wow! Some minor notes: Ctrl-Shift-Enter (Cmd-Shift-Return on a Mac) creates an array formula. Paste the formula as usual, but rather than Enter hit Ctrl-Shift-Enter to save it. It will then show between curly braces. In OpenOffice.org on a Mac, I could not drag down the formula for the next rows (as it would yield some super array formula then). But copy & paste does work, so I'm sure there's some shortcut as well. And OpenOffice.org does not know FINDB; using FIND seems to work as well.
    – Arjan
    Sep 20 '09 at 21:39
  • Elegant solution!
    – wilson
    Nov 15 '11 at 10:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.