Discussions

Expand all | Collapse all

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

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

    Posted 07-19-2017 18:08
    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 and use a different text field's number. In MS Access there's a nice IsNumeric() function that will return true/false after testing a text string, but I see nothing of the sort in the function list for QuickBase. is there an easy, tricky way around this limitation?


  • 2.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:17
    There may be other ways but this might be quick and dirty as a formula checkbox field.  But this will fail if the number has a leading zero like 0123.  If that is possible, then the formula will need to be a bit fancier.


    ToText(ToNumber([maybe a number]))=[maybe a number]


  • 3.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:27
    Thanks Mark. That's clever, but I couldn't get it to work. For my current purposes, this seems to have done the trick. 

    Length([Subject Form ID]) > 0 and Left([Subject Form ID],1) >= "1" and Left([Subject Form ID],1) <= "9"

    It really bugs me, though, that there isn't a native function to do something so straightforward and necessary.


  • 4.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:36
    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.


  • 5.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 02-19-2018 19:27
    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])


  • 6.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 02-19-2018 19:31
    to test for a blank you would use

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

    ie you test against empty quotes


  • 7.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 02-19-2018 19:59
    True. I agree.


  • 8.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:42
    Yeah, I would have though it would work for me,but it didn't. The particular piece of text that was a problem was 'Oneil  (starting with an apostrophe). For my purposes, I am extracting FormID's which are all positive integers from 2 to 5 digits long, so I took the simple way to just test the first character, but I would like to get your more universal approach to work too, for what I'm sure will be later needs for this functionality.


  • 9.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:49
    I tried 'Oneil on my formula and it correctly said it was not a number


  • 10.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 07-19-2017 18:52
    Hmm. Thanks for letting me know. I'll have to go back and do some more testing when I have time. Too much else going on with this project right now - I need to keep moving. Thanks again for your help!


  • 11.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 02-20-2018 10:01
    I would avoid getting into a situation where you have either text or a numeric value in a text field in the first place. Rethink your fields and calculations. Ambiguous data (text/numbers) should be cleaned up at the point of key entry or importing.

    You probably could do a simple formula knowing that a number in a business setting consists of a {set of digits} possibly proceeded by a plus or minus sign and at most one decimal point  amid the digits but it is just an exercise if difficulty.

    QuickBase does not use scientific notation for numbers but technically you could have this in your string and ToNumber () will convert it correctly.

    ToNumber("34e5") => 3400000

    Or you could use JavaScript where any manner of parsing and data conversion is possible. Anyone here that could write a formula to accomplish the task you ask about could do it easier in JavaScript. Replace the formula language with JavaScript is my advice.

    It strikes me as extremely odd that for a no code platform the first thing you have to do is master the formula language and all its quirks.   _\_(_)_/__


  • 12.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 04-30-2019 15:04
    Sorry to resurrect an old post but this is the closest post I've found that's similar to the problem I'm struggling with. 
    I have a text field that's supposed to have all numbers except for dashes. I need to do IsNumeric type of validation. While Mark's formula is working great, my field can start with 0 so I can't use it. And changing the field to numeric is not an option at this point due to other dependencies. I've tried many different approaches with no luck. Any help would be greatly appreciated. 


  • 13.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 04-30-2019 16:16
    if it starts with a 0, why not create a formula to remove the initial 0?


  • 14.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 04-30-2019 19:33
    When I needed to verify something was a number(mine had 2 dashes between the numbers) I made a formula checkbox that would trigger if it was not a good number. I removed the dashes with a Part formula and then put all 3 parts back together in a variable. Then I tested to see if the number was above or below a certain number. My number was similar to a phone number so I checked if it was more than 0000000000 and less than 9999999999. If it returned true then it was a number. If it returned false it was not and I could abort the save.

    The main issue with this approach is that for form rules to take effect the person needs to enter the data in the field and then click outside of the field to another one. If they do not do this then the form rule fails and the save happens.

    I came to the conclusion that the best way to do this is with JavaScript. You can actually validate data with that and prevent people from entering the wrong thing. It's a nightmare doing it native in QuickBase imo.


  • 15.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 04-30-2019 20:16
    Can you tell me what the format is, especially how many digits there are, excluding the dashes?  Maybe post a typical example?


  • 16.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 05-01-2019 14:36
    It's SSN field that allows either null, all 9 digits or just last 4 digits. So the format is either empty, 123-456-789 or 0123.


  • 17.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 05-01-2019 15:00
    Here is a good strat to validating the SSN

    var text RemoveDashes = SearchAndReplace([SSN],"-","");

    var text DigitOne =   Mid($RemoveDashes,1,1);
    var text DigitTwo =   Mid($RemoveDashes,2,1);
    var text DigitThree = Mid($RemoveDashes,3,1);
    var text DigitFour =  Mid($RemoveDashes,4,1);
    var text DigitFive =  Mid($RemoveDashes,5,1);
    var text DigitSix =   Mid($RemoveDashes,6,1);
    var text DigitSeven = Mid($RemoveDashes,7,1);
    var text DigitEight = Mid($RemoveDashes,8,1);
    var text DigitNine =  Mid($RemoveDashes,9,1);

    (Contains("0123456789", $DigitOne) or $DigitOne = "")
    and
    (Contains("0123456789", $DigitTwo) or $DigitTwo = "")
    and 
    (Contains("0123456789", $DigitThree) or $DigitThree = "")
    and
    (Contains("0123456789", $DigitFour) or $DigitFour = "")
    and
    (Contains("0123456789", $DigitFive) or $DigitFive = "")
    and
    (Contains("0123456789", $DigitSix) or $DigitSix = "")
    and
    (Contains("0123456789", $DigitSeven) or $DigitSeven = "")
    and
    (Contains("0123456789", $DigitEight) or $DigitEight = "")
    and
    (Contains("0123456789", $DigitNine) or $DigitNine = "")
    and
    (Length($RemoveDashes)=0 or Length($RemoveDashes)=4 or Length($RemoveDashes)=9)


  • 18.  RE: Is there a way to test in a formula if a text field contains a valid number?

    Posted 05-02-2019 16:26
    This great, thank you!!! Funny enough, I just had an idea to use contains last night but hadn't actually implemented it.