=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