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])
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    to test for a blank you would use

    IF(Trim([my text field])="", true)

    ie you test against empty quotes
  • SuryaAvantsa1's avatar
    SuryaAvantsa1
    Qrew Member
    8 years ago
    True. I agree.