Hi All,

I am trying to separate cells containing data like AM022325F or MA5419AMS into 3 columns. The first columns should translate the fisrt 2 characters (ma or am). The second the numbers (5225419, 0325) and the third should return the last characters. These can be 1, 2 or 3 characters, so just F, AMS, or NS.
Note all the contents can vary. Only AM or MA are fixed. The numbers can be in sets of 4 or 5, and the letters at the end can also vary as said above.

I am using the following formulas:
column 1
=1*MID(A11,MATCH(TRUE,ISNUMBER(1*MID(A11,ROW($1:$9),1)),0),COUNT(1*MID(A11,ROW($1:$9),1)))
to extract everything from the cells, and keep the numbers only.

then:
column 2
=LEFT(A11,2)
to keep the first two characters

and finally:
column 3
=TRIM(SUBSTITUTE(SUBSTITUTE(A18,C18,""),F18,""))
to substract columns 1 and 2 from the original, to be able to keep the last few characters. The only way of doing this, since the last characters vary in length.

This seems to work most times, but if I have a munber like AM0251F, I get 0251 in column 2 and 1F. BUT i should only get F, not 1F.
It seems that excel does not take into account the front 0...

Can anyone help me out here.
I've been struggling with this for a while now.

Thanks

Gerry