# Sorting of a alphanumeric string problems

#### Pardus

##### New Member
Good Day,

i want to sort the following:

 R1P3P R10P2P4P R1P R10P R3P R10P2 R2P

<colgroup><col></colgroup><tbody>
</tbody>

When i sort it normally it looks like this:
 R10P R10P2 R10P2P4P R1P R1P3P R2P R3P

<colgroup><col></colgroup><tbody>
</tbody>

What i want it to sort like is
 R1P R1P3P R2P R3P R10P R10P2P R10P2P4P

<colgroup><col></colgroup><tbody>
</tbody>

How can i do this?

i tried to use the "text" function , =text(a1,"@") and =text(a1,"###"), but it still do not sort it correctly.

Can anyone help?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Mackers

##### Well-known Member
Hi Pardus

It looks like you want to sort based on the values of the numbers following R, P, and P.

The way I would do this is pretty messy but would involve cutting the cells up into several columns using several formulas, then either recombining them in a more sensible fashion or using a custom sort. For example, say if your original names were in column A, I would have the R in column B, the first number (1, 2, 3 or 10) in column B, the letter P in column C, the second number in column D (2 or 3, or a zero if not present) etc. You could then stick the numbers back together and sort numerically, so R10P2P4P would have returned R, 10, P, 2, P, 4, P, and I could recombine the numbers as 1024. R1P3P would have returned R, 1, P, 3, 0 and I would recombine the numbers as 130.

In order to actually cut these cells up you will have to write some formulas. Assuming that your names are always in the format R#P (at least), cutting the first number out of a cell can be done as follows:

=--MID(E22, 2, FIND("P", E22, 2)-2)

To get the others you'll have to use some grey matter. There is probably a much cooler way of doing this but it's beyond me at the moment

Hope that helps

Mackers

Replies
3
Views
203
Replies
13
Views
810
Replies
3
Views
791
Replies
1
Views
235
Replies
3
Views
648

1,195,919
Messages
6,012,304
Members
441,690
Latest member
CyberWrek

### 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.

### Which adblocker are you using?

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