separate strings into cells

[Mega_gerry]

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

[ChemistB]

Here's a solution
In B1
=LEFT(A1,2)
In C1 (an Arrayed Formula)
=MID(A1,3,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),1))-2)
In D1 (an Arrayed formula)
=MID(A1,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),1))+1,10)
Arrayed formulas must be entered with CNTRL SHFT ENTER instead of ENTER. If done properly, you'll see {} around your formula.
Does this work for you?

[djapigo]

ChemistB... if a number ends with a 0, it's not picking it up... ideas? example... AM0200F... you get AM, 02, 00F

[Mega_gerry]

Hi Christian,

Thanks for your prompt reply!

Unfortunatelly this does not seem to work, since the number of numbers in the middle can change: there can be from 3 to 6 numbers...
Any clues?

Thanks

[djapigo]

Mega,

Chemist formula accounts for any numbers between the letters... it's the trailing 0 that's not getting picked up for some reason...

Try his formula and copy it to any of your examples...

[ChemistB]

Yep, my solution only works if the final digit occurs only once because it stops once it finds the specific digit. Back to the drawing board. doesn't matter if it's a 0 or a 8, if its a duplicate, it never reads it.

[Jonmo1]

Try

A1 = original string
B1: =LEFT(A1,2)
C1: =MID(A1,3,LOOKUP(2,1/ISNUMBER(MID(A1,ROW(A1:A10),1)+0),ROW(A1:A10))-2)
D1: =SUBSTITUTE(A1,B1&C1,"")

[Mega_gerry]

Chemist,
OK, I might have mistyped something. The formula works, but indeed needs a patch since it's not picking up the last 0 or any repeated number, as you said....
On the right track though.

[tigeravatar]

Gerry,

Welcome to the forum!
Attached is a sample workbook based on the criteria described.
Row 1 is a header row, so actual data starts in row 2
Column A is the original string
Column B is the first part with the first two letters, defined by this formula:

Please Login or Register  to view this content.

Column C is the second part with the numbers, defined by this formula:

Please Login or Register  to view this content.

Column D is the third part with the trailing letters, defined by this formula:

Please Login or Register  to view this content.

[Mega_gerry]

Hi Tigeravatar!

That did it!
Seems to work just fine!

THANK YOU ALL SO MUCH!!!