I am trying to sort mixed text strings. This is something I frequently have to do and I have so far had to resort to using 'Text to Columns' and then heavily edit the results. I dream of having a formula or three that will do the job...

To this end, I am trying to write some formulas. They are working for some of my text strings but not for others... and there are two different errors...

Column A has my text strings

Column H has my first formula

Column I has my second formula

The text string starts with 1-3 letters, then a series of numbers, one more letter and some more numbers. They are interspersed with decimal points and spaces.

Examples of text strings - Result of Formula 1 - Result of Formula 2

G155.5 .B72 2013 - G - 155.5 .B72 2013

LB1139.25 .W35 2013 - LB - 1139.25 .W35 2013

P51 .G37 2000 - P5 - 1 .G37 2000

TX361 .A8 N87 2006 - #NAME? - #NAME?

KUD50 .G27 2012 - #NAME? = #NAME?

As you can see above, the first two are successful. The third is almost successful (the '5' should be in the 2nd result, not the first) and the last two have failed completely.

My two formulas are -

=IF(MID(A2,2,1)={"1","2","3","4","5","6","7","8","9"},LEFT(A2,1),IF(MID(A2,3,1)={"1","2","3","4","5","6","7","8","9"},LEFT(A2,2),IF(MID(A2,4,1)={"1","2","3","4","5","6","7","8","9"},LEFT(A2,3),No)))

=MID(A2,LEN(H2)+1,LEN(A2)-LEN(H2))

The success of the 2nd formula obviously depends on the success of the first...

Ideally, I would love to have the second formula give me up to the first space (ex. 155.5 or 1139.25 or 51) but I haven't yet figured out how to write that into the 2nd formula...

Any assistance would be greatly appreciated!

David