Forum Discussion

ScottPugh's avatar
ScottPugh
Qrew Cadet
6 years ago

Checking if characters in a text string are all numeric

Looking for ideas for how I might check each individual character in a text string to ensure its a number.    If all characters are numbers the result should be 'true'.   If even 1 character is not numeric then the result should be 'false'

------------------------------
Scott Pugh
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    This seems to work for me to validate the field called [data entry].  A blank entry will also qualify as being OK using this formula.

    var text DataEntryConvertedToNumberAndbackToText = ToText(ToNumber([data entry]));

    $DataEntryConvertedToNumberAndbackToText = [data entry]
    and
    Length($DataEntryConvertedToNumberAndbackToText)= Length([data entry])

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      I realized after posting (perhaps with a prompt from Scott) this that it would fail on very large numbers.  That is because there is a limit (probably 13 characters) of the largest number which Quick Base can handle without rounding.

      So here is another formula which should work for any length of text.

      var text DataEntry = [data entry];  // insert your field

      var text ReplaceZero= SearchAndReplace($DataEntry,"0","");
      var text ReplaceOne= SearchAndReplace($ReplaceZero,"1","");
      var text ReplaceTwo= SearchAndReplace($ReplaceOne,"2","");
      etc
      var text ReplaceNine= SearchAndReplace($ReplaceEight,"9","");


      If(Length($ReplaceNine)=0, true)


      The gist of this is to successively replace the 0's. then the 1's .. up to the 9's with a nothing (empty quotes).  Any dregs remaining must not be 012345678, or 9.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------