I recently had a customer ask if I could help him sort part numbers. The problem was that the part numbers start with one or more letters followed by 2 to 7 digit number and sometimes a dash in between. Since the field is a string, and since the numbers vary in length, the sort doesn’t work as expected:

APD009
B-1025
B326
B-52

(See this article for why dashes are sometimes ignored in sorting. )

So here are the steps to get these to sort in a meaningful way. First I wrote a formula to determine how many initial characters there are. This is based on a formula I wrote about before that was designed to strip all numbers off of the right end of a string:

//{@Initial Chars}
Local StringVar x := replace ( {ITEM.ITEMNO} & '1' , '-' , '' );
Local NumberVar y := Length( Totext( Val( strReverse( x ) ) ,0,'') );
if length(x) = y then 0 else length(x) - y

Then the remaing steps are:

1) Eliminate the dashes.
2) Strip off the initial letters and convert the remainder to a numeric using Val().
3) Use Totext () to convert the number into a zero-padded string
4) Tack the initial letters back on.

With local variables you can see the process, step by step.

Local StringVar x;
Local NumberVar y;
x := Replace ({ITEM.ITEMNO} , '-', '') ;
y := val (x [ {@Initial Chars} +1 to 99 ]);
x [ 1 to {@Initial Chars} ] & Totext (y, '0000000')

The resulting values would look like this if you placed them on the report. But these would probably only be used for sorting and not displayed.

APD0000009
B0000052
B0000326
B0001025