# Best way to remove leading zeros from a non-numeric value in Excel

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

000000123
000001DA2
0000009Q5
0000L210A
0000014A0
0000A5500
00K002200

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

123
1DA2
9Q5
L210A
14A0
A5500
K002200

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:

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

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.

• After many attempts at this, this is the only version which will work without resorting to VBA. 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). 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. 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. 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.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"0"," "))," ","0")
• 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) 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...? 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.

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

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

=RIGHT(B1,MATCH(B1,\$A\$1:\$A\$10,1)-1)

• Smart! As for the third parameter in the MATCH function: 1 = find the largest value less than or equal to lookup_value 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. Sep 20 '09 at 21:39
• Elegant solution! Nov 15 '11 at 10:44