# 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. – Lance Roberts 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. – Chris Farmer Sep 17 '09 at 19:50
To be explicit, there are never embedded spaces. Just 0-9 and A-Z. – Chris Farmer Sep 17 '09 at 19:58
Edited to include those trailing zero examples – Chris Farmer 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). – Lance Roberts 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. – skamradt Sep 17 '09 at 21:36
Thanks. I like this one. Cells are cheap. – Chris Farmer Sep 18 '09 at 15:12
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

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 – Arjan Sep 18 '09 at 7:09

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`) – 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). – Lance Roberts Sep 17 '09 at 20:15
The CLEAN function saves the embedded spaces, but also saves all but the first leading space. – Lance Roberts 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. – Lance Roberts Sep 17 '09 at 20:41

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

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

## protected by Ivo Flipse♦Jan 13 '11 at 16:20

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).