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.

6

5 Answers

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.

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

2

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))

2

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")

8

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)

1

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.

2