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 nu...
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
    ------------------------------