I need to remove all the leading zeros from a part number column on my
spreadsheet by formula in an adjacent column. There can be none or up to 4
zeros at the beginning, and some entries are alpha-numeric. (So multiplying
by 1 or adding 0 won't work.) The problem comes when trying to perform a
vlookup. If I don't enter the zero's, I get a "not found". If there is a way
for vlookup to ignore the leading zero's that will work too.

Thanks!

Squeaky

You can use the SUBSTITUTE formula to substitute all zeroes with an empty
string.

=SUBSTITUTE(A1,0,"")

Where A1 is the cell containing your part number
0 is the character you want to replace
and "" is what you want to replace the zero with
--
Kevin Backmann


"Squeaky" wrote:

I need to remove all the leading zeros from a part number column on my
spreadsheet by formula in an adjacent column. There can be none or up to 4
zeros at the beginning, and some entries are alpha-numeric. (So multiplying
by 1 or adding 0 won't work.) The problem comes when trying to perform a
vlookup. If I don't enter the zero's, I get a "not found". If there is a way
for vlookup to ignore the leading zero's that will work too.

Thanks!

Squeaky

Hi Kevin,

Thanks for the quick response. I tried that formula but it erases all
zero's, not just the leading ones.

"Kevin B" wrote:

You can use the SUBSTITUTE formula to substitute all zeroes with an empty
string.

=SUBSTITUTE(A1,0,"")

Where A1 is the cell containing your part number
0 is the character you want to replace
and "" is what you want to replace the zero with
--
Kevin Backmann


"Squeaky" wrote:

I need to remove all the leading zeros from a part number column on my
spreadsheet by formula in an adjacent column. There can be none or up to 4
zeros at the beginning, and some entries are alpha-numeric. (So multiplying
by 1 or adding 0 won't work.) The problem comes when trying to perform a
vlookup. If I don't enter the zero's, I get a "not found". If there is a way
for vlookup to ignore the leading zero's that will work too.

Thanks!

Squeaky

Try this

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537762

Squeaky,

the following formula will return the cell excluding the leading 0's. I
took in mind your statement that there will be at most 4 0's (hence the
1:4)

=MID(A2,MIN(IF(MID(A2,ROW(1:4),1)="0",ROW(1:4))),L EN(A2))

It is an array formula, hence it should be committed with
Shift+Ctrl+Enter.

HTH
Kostis Vezerides

daddylonglegs Wrote:
Try this

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

...or even

=MID(A1,FIND(LEFT(SUBSTITUTE(A1,0,"")),A1),255)

removes any number of leading zeroes


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537762

I got the two from daddyLL to work, had some trouble with the 3rd one, not
sure why. I had solved it by using nestled IF functions to look for a leading
zero multiple times, but I will use daddyLL's second suggestion.

Thanks for all the help!

Squeaky

"Squeaky" wrote:

I need to remove all the leading zeros from a part number column on my
spreadsheet by formula in an adjacent column. There can be none or up to 4
zeros at the beginning, and some entries are alpha-numeric. (So multiplying
by 1 or adding 0 won't work.) The problem comes when trying to perform a
vlookup. If I don't enter the zero's, I get a "not found". If there is a way
for vlookup to ignore the leading zero's that will work too.

Thanks!

Squeaky