Sorting a mixed text string

[CrazyDave]

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

 

[JoeMo]

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

Can you explain how you want to partition a string?

 

[konew1]

Try this. If it doesn't do what you need then for each of your data line please specify exactly what you want returned

Excel Workbook
	A	B	C
1	G155.5 .B72 2013	G	155.5
2	LB1139.25 .W35 2013	LB	1139.25
3	P51 .G37 2000	P	51
4	TX361 .A8 N87 2006	TX	361
5	KUD50 .G27 2012	KUD	50
Sheet16

 

[Rick Rothstein]

Try this. If it doesn't do what you need then for each of your data line please specify exactly what you want returned

Excel Workbook
	A	B	C
1	G155.5 .B72 2013	G	155.5
2	LB1139.25 .W35 2013	LB	1139.25
3	P51 .G37 2000	P	51
4	TX361 .A8 N87 2006	TX	361
5	KUD50 .G27 2012	KUD	50
Sheet16

Here is a shorter formula for cell B1...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

 

[CrazyDave]

Many thanks to konew1 and Rick!!! Your formulas are working brilliantly!!! I can't wait to apply them to a larger set of data than the batch I am currently working with!!!