Alphanumeric sort order challenge

[boone]

In Excel, I would like to perform an alphanumeric sort of the following data and have the results be in the same sort order:
A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, A13, A14, A15, A16, A17, A18, A19, A20, A21, A22, A23, A24, A25.

Instead of the traditional left to right, character by character resulting in the following sort order:
A1, A10, A11, A12, A13, A14, A15, A16, A17, A18, A19, A2, A20, A21, A22, A23, A24, A25, A3, A4, A5, A6, A7, A8, A9.

Any help I could get on this issue would be GREATLY appreciated.

Thanks in advance!

 

[Richard Schollar]

Hi

One way would be to use a helper column containing the following formula:

=LEFT(A1,1) & TEXT(--RIGHT(A1,LEN(A1)-1),"00")

and then select both columns and sort ascending by the helper column (which will sort in the desired order) which will result in your original column sorting in the desired order.

Make sense?

richard

 

[boone]

Thanks Richard. That works!

I didn’t tell you the whole story in the original posting, but I was able to modify your formula to accommodate similar but different data (i.e. AA1, AA2, AA3, etc). It takes a couple extra steps but it works great.

Thanks again,
Ron