Recently a customer needed to group records using part of a code field. The code field had alpha characters followed by a series of numbers. They wanted to to strip off any numbers on the end and group using only the initial string portion of the value. In the past I have done things like this using a FOR loop to check each character. This time another idea popped into my head. If you reversed the string so it started with the numbers you could put it inside the VAL() function. This would convert the numeric portion to a number and ignore the string characters that followed. You could then check the length of that number and know how many characters to take off the end of the value. To get the length of the number you would have to turn it back into a string and then use the Length function. So the calculation would nest four functions like this:

Length( Totext( Val( strReverse( {table.field} ) ) ,0, "" ) )

The only glitch was if the string ended with a zero, like “XXX1230”. In those cases the VAL() would ignore the zero (true numbers don’t start with a zero) and then my length would be off by one. To fix that I appended a “1” to the end of each string before I ran it through the calculation. That way the value would never end with a zero.

Last, I created a local variables so that a user would only have to put their field name in on place. The end result looks like this:

//add a one to deal with a final zero
Local StringVar x := {Customer.Postal Code} & '1';
//calc the # of digits to remove
Local NumberVar y := Length(Totext(Val(strReverse(x)),0,''));
//if it is all number characters return a blank.
if y = length(x) then ""
// otherwise reduce the length by y positions.
else x [ 1 to length(x)- y ]