Sorting of a alphanumeric string problems

[Pardus]

Good Day,

Please help!!!!!

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?

 

[Mackers]

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