G'day everyone,
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
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