Sorting Data where there is an alpha character at the end of the number

[howard]

I have account numbers the I need to sort as follows:

All the numbers ending in an X to be sorted first in ascending order (eg 25X, 31X etc), then all the other numbers must be sorted in asecending order for eg 1001, 1001A, 1001B, 1002 etc


Your assistance in resolving this will be most appreciated

 

[RonB1111]

Create a helper column to sort by and use this formula:
=IF(RIGHT(A2,1)="X","1-","2-")&TEXT(IF(ISNUMBER(--RIGHT(A2,1)),A2,--LEFT(A2,LEN(A2)-1)),"0000")&IF(ISNUMBER(--RIGHT(A2,1)),"",RIGHT(A2,1))

This assumes account numbers can have up to 4 numeric positions at the front and either 1 or 0 alpha characters at the end.

 

[howard]

Hi Ron

Thanks for your help.This is a very clever formula It does the trick

Regards

Howard