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