The effect of dashes on the VAL() function

Monday 25 August 2008 @ 11:30 pm

All of my students learn how to use the VAL() function, which converts numeric characters into true numerics. The VAL() function works by starting at the left of the string, reading the digits, and stopping when it hits the first alpha character. It then converts the numeric characters it has found into numbers. However, if it finds a dash before it finds an alpha character, it flips the sign of the numeric result to negative and keeps reading the digits.

The problem comes when the dash is among the numeric characters. For instance a string might be “12-14”. The VAL() function would convert the suite number portion into a negative value, -1214. To prevent this, use the Replace() function within the VAL() to eliminate the dashes and replace them with either “” (empty string) or ‘x’.

Use “” if you want the VAL() to include the numbers after the dash in the result.
Use “x” if you want the VAL() to ignore the numbers after the dash.
Below is the syntax using the ‘x’:

Val( Replace ( {Customer.Address2} [ 7 to 15 ] , ‘-‘, ‘x’ ) )

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro