Sorting text by numbers

[vmon]

I have a column that has data I need to sort on. The column is a text field and contains numbers and letters. Could have 1 to 3 numbers in start and 1 or 2 letters follow for half the list and 1 letter in start and 1 to 3 numbers follow. Like this.

10A
19A
20C
2B
2C
402AL
430B
482CL
a2
c2
j1

I need it to sort like this and have no idea how to get it done. I know why it sorts above and havenot found much help with "format".

2B
2C
10A
19A
20C
402AL
430B
482CL
a2
c2
j1

Thanks,
vmon

 

[dcx693]

Use a query to:
-Extract the numeric portion of the string.
-Format all the numeric pieces the same way (leading zeroes is typical)
-Sort on the column of formatted numbers, but don't display it.
-Your original column will now be in the correct sort order.

The first part is the hardest (extracting the string). If the numbers lead off the string, you can use the Val() function. Otherwise, you'll need to use a custom function. I thought I'd recently seen a post about that very thing.