Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 3 down vote favorite
2

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.

share|improve this question
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
show 1 more comment

4 Answers

up vote 8 down vote accepted

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.

share|improve this answer
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
up vote 5 down vote

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)

share|improve this answer
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
up vote 3 down vote

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")
share|improve this answer
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
show 3 more comments
up vote 3 down vote

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.

share|improve this answer
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

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

This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.

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