Another Zero problem...
 

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

Another Zero problem...
 

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

Another Zero problem...
 

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

Another Zero problem...
 

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

Another Zero problem...
 

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

Another Zero problem...
 

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

Another Zero problem...
 

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