Problem Sorting Alpha Before Numeric

[new123]

I have a string of data, which will always be 6 characters long, but I need to sort alpha before numeric, which isn't normal excel convention. For example, an ascending sort would need to be A00511, UNIT14, U28FS4, U28HN3...not A00511, U28FS4, U28HN3, UNIT14

Any help would be greatly appreciated. Thanks!

 

[Gary McMaster]

Check out "Custom Lists" in Excel help. It may work if your list of values is not un-limited.

I've used it numerous times for printed circuit component designators:

R1, R2, R3 ... R10 ... R20 etc.

You can apply the custom list as a "sort pattern". In my above example R10 sorts after R9 and not after R1 like it usually would.

You can key the list into the custom list dialog or import it from a specified range of cells. I have no idea what the max number of list elements is.

Hope that helps.

Gary

 

[new123]

Unfortunately, the list is unlimited. I've played with the custom list, but haven't been able to find anything that works. Essentially, whenever a new security is added, a "ticker" is assigned to the security. I have no control over the ticker; it can be anything. The system sorts the ticker by alpha and unfortunately I can change how our system sorts the tickers.