Forum Discussion

MichaelBarrow's avatar
MichaelBarrow
Qrew Cadet
8 years ago

Is there a way to test in a formula if a text field contains a valid number?

I have a complex text formula field that extracts out text from another text field. If it contains a number, I want to use it in another formula field's calculations. If not, I want to skip over it an...
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
8 years ago
I did test that formula and it did seem to work for me ... did you have a string that failed that I could test it on.  The useful idea is that the ToNumber( ) function will return blank it what you give it contains non numeric characters.

In my test, the field for [maybe a number] was a text string.
SuryaAvantsa1's avatar
SuryaAvantsa1
Qrew Member
8 years ago
That could be a problem. What if it was a blank? And the tonumeric also returns a blank. Then they both will be equal, thus misleading us to believe that a blank is numeric. 

I guess, we need to first filter out blanks and call them non-numeric right away before checking for non-blanks.

if (Contains([may be a number]," ",false,ToText(ToNumber([maybe a number]))=[maybe a number])