A1,A2,A11,A12 sorts by A1,A11,A12,A2

[jfrost10]

I have a field thats of type string, and the user stores alpha-numeric values in it. How can I sort them though so that they'll come out correclty?

i.e. A1,A2,A3,A11,A12,A23 comes out as
A1,A11,A12,A2,A23,A3
???

Thanks,

Jack

 

[John43]

Have users make entries A01, A02, A11, A23 etc.

 

[MikeCoch]

Make two seperate fields for sorting purposes. One Alpha and one numeric.

A1 would be A and 1.
A2 would be A and 2.
A11 would be A and 11.

 

[ScottGS]

Jack,

You could create a formula which massages the value into a sortable form, then sort on the formula.

You would need to parse the string to split it into the alpha and number parts, then recombine them with sufficient leading zeros or spaces to accomodate the largest number you will encounter.

If you know there will only be a single letter column, then it is easy. Use this formula (v8.5 Basic syntax):
formula = Left({field},1) & ToText(Val(Mid({field},2)),"000&quot
Put as many zeros in as your largest number has digits.

If there can be more than one letter, then you will need to search for the last letter, or first digit, to find the break point. I am assuming the form is always <letter(s)><digit(s)> and that there would not be letters following digits.

Scott