Sort function

[Gary Case]

I want to sort a column which has entries such as 1st,
2nd, 3rd, 4th, 5th, etc. However, after I sort, 10th,
11th, and 12th come before 2nd, 3rd and 4th.

Is there a way around this without putting a zero in front
of the single digit numbers?

Thanks

 

[Dave Peterson]

You could use a helper cell to extract the numeric portion of the string. Then
sort by that helper cell column.

=0+LEFT(A1,MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)

If your 234th is in cell A1, this will return 234 (as a number).

 

[Harlan Grove]

You could use a helper cell to extract the numeric portion of the string.
Then sort by that helper cell column.

=0+LEFT(A1,MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
0)-1)

....

In English ordinals such as the OP's are always formed by appending 2 and
only 2 letters to digit strings, so

=--REPLACE(TRIM(A1),LEN(TRIM(A1))-1,2,"")

should be sufficient. If you're assuming the strings could contain junk
after the 2 ordinal-producing letters, couldn't there also be junk before
the first decimal digit?

 

[Dave Peterson]

I don't quite remember how many alpha characters I was thinking about. But I'm
pretty sure it wasn't 2!

But this might even be easier:
=0+LEFT(A1,LEN(A1)-2)
(add trim() if required)

 

[Harlan Grove]

...
...

But this might even be easier:
=0+LEFT(A1,LEN(A1)-2)
(add trim() if required)

...

Good point, though I'd always use TRIM.