Discussions

Expand all | Collapse all

Checking if characters in a text string are all numeric

  • 1.  Checking if characters in a text string are all numeric

    Posted 01-10-2020 12:10
    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
    ------------------------------


  • 2.  RE: Checking if characters in a text string are all numeric

    Posted 01-10-2020 12:24
    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
    ------------------------------



  • 3.  RE: Checking if characters in a text string are all numeric

    Posted 01-13-2020 18:18
    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
    ------------------------------