Strip leading zeros
 

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

Strip leading zeros
 

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

=0+A2

then use general formatting


--


Regards,


Peo Sjoblom


"Compass Rose" wrote in message
...
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

Strip leading zeros
 

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),2 55)



Compass Rose wrote:

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

--

Dave Peterson

Strip leading zeros
 

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" wrote:

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),2 55)



Compass Rose wrote:

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

--

Dave Peterson

Strip leading zeros
 

=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.



Compass Rose wrote:

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" wrote:

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),2 55)



Compass Rose wrote:

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

--

Dave Peterson


--

Dave Peterson

Strip leading zeros
 

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


"Compass Rose" wrote in message
...
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" wrote:

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),2 55)



Compass Rose wrote:

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

--

Dave Peterson