Strip leading zeros

[Compass Rose]

Using Excel 2003

We manufacture products that have 4 properties, stored in columns B, C, D
and E. I create a part number for each product by concatenating the data in
the four columns. The property in Column D can be from 1 to 6 characters in
length, so when I create the part number, I apply the following formula to
the data in column D:
RIGHT("00000"&D1,6)
to ensure that all part numbers will be of the same length. When the part
number is created, it is 13 characters long.

What formula can I apply to the part number to extract the original property
in column D, stripping away only the leading zeros?

TIA
David

 

[Peo Sjoblom]

If the part number are only numbers you can just force a calculation like

=0+A2

then use general formatting


--


Regards,


Peo Sjoblom

 

[Dave Peterson]

I'm not sure I understand the layout of your data, but this will remove the
leading 0's in A1:
=MID(A1,SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1),255)

 

[Compass Rose]

Thanks, Dave. This is exactly what I'm looking for. I'm also trying to
decifer the formula to learn from your example.

 

[Dave Peterson]

=substitute(a1,"0","")
removes all the zeros from in A1:
000001231A001234
becomes
1231A1234

Then =left("123aA1234",1) picks out the first character in that string ("1").

=mid() looks for the position of that character.

255 is a big number that means bring back that many characters. You could use
12 (I think) if you wanted.

 

[Rick Rothstein \(MVP - VB\)]

To see how Dave's formula works, let's look at a step-by-step example. For
this example, we will assume A1 contains 0000980AB0765 (although the length
and composition of the value is not important).

SUBSTITUTE(A1,"0","") produces 98AB765

LEFT(SUBSTITUTE(A1,"0",""),1) produces 9

SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1) produces 5 which is the position of
the first 9 in 0000980AB0765.

The MID function then uses the 5 as its starting point in A1 (we know this
the first character after all the zeroes) and returns up to the next 255
characters (it doesn't matter if the text is less than 255 characters... the
255 just needs to be larger than the longest text after the first non-zero
characters which, for your setup, could have been 13).

Rick