Sorting a mixed text string

CrazyDave

New Member
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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?

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
ABC
1G155.5 .B72 2013G155.5
2LB1139.25 .W35 2013LB1139.25
3P51 .G37 2000P51
4TX361 .A8 N87 2006TX361
5KUD50 .G27 2012KUD50
Sheet16

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
ABC
1G155.5 .B72 2013G155.5
2LB1139.25 .W35 2013LB1139.25
3P51 .G37 2000P51
4TX361 .A8 N87 2006TX361
5KUD50 .G27 2012KUD50
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)

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!!!

Replies
7
Views
435
Replies
3
Views
263
Replies
3
Views
205
Replies
7
Views
188
Replies
7
Views
585

1,196,501
Messages
6,015,568
Members
441,902
Latest member
alhaste

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back