String Sort

[macinjosh1]

I've got a list of warehouse locations that are a mixed alphanumeric string. For example

A1A1
A1A2
A1A10
A10AA1

So there's always 4 parts, letter, number, letter, number. How can I get this list so that it sorts in order? The letters would be A-Z then AA-ZZ, and the numbers 1 to whatever.

Thanks.

 

[mikerickson]

If you use 2 digit numerals, that will help things sort "AA01AB02" instead of "AA1AB2"

The other part is more annoying, one could us leading spaces " A01 B02" instead of "A01B02"

Another approach would be to use 4 columns and a custom list A,B,C,...,Z,AA,AB,... to sort things properly.

What is the highest potential alphabetic entry? It sounds like it bigger than AA but less than AAA, is there a more precise limit?

 

[macinjosh1]

The numbers will defiantly be 2 digits, but could get up to 3. I don't see the letters getting passed 2.

I was thinking about putting it into 4 different columns, but how do I pull out each section into a different column if the length is variable?