Forum Discussion

Qrew Trainee

2 years ago

Phone Number Brackets

Hi Is there a formula that removes the brackets from a phone number when a csv is exported from Quickbase? Other than changing the field to numeric ------------------------------ Jessica Mateng...

Formulas and functions

MarkShnier__You

Qrew Legend

Try this. Assumes a North American starting format.

var text Tel = ToText([My Phone# field]);

var text RemoveBracketOne = SearchAndReplace($Tel,"(","");
var text RemoveBracketTwo = SearchAndReplace($RemoveBracketOne,")","");
var text RemoveHyphen = SearchAndReplace($RemoveBracketTwo,"-","");

$RemoveHyphen

phone# to 10 digits prefixed with +1

var text Tel = ToText([Mobile for Drug test Notifications]);

var text RemoveBracketOne = SearchAndReplace($Tel,"(","");
var text RemoveBracketTwo = SearchAndReplace($RemoveBracketOne,")","");
var text RemoveHyphen = SearchAndReplace($RemoveBracketTwo,"-","");

SearchAndReplace($RemoveHyphen," ","")

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

JessicaMatenga

Qrew Trainee

2 years ago

Thanks Mark that worked perfectly with the mobile numbers. For the phone number is also removed the brackets and hyphen but there is a space after the closing bracket so converted the space appears -
029 7963100 or 5 5937586

Just need to remove the space as well if you can supply the additional formula.
Thanks

------------------------------
Jessica Matenga
------------------------------

MarkShnier__You
Qrew Legend
2 years ago
The formula should have removed the space too. Can you post your whole formula?

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
- JessicaMatenga
  Qrew Trainee
  2 years ago
  Hi Mark
  Here is the formula for the Phone Number field -
  
  var text Tel = ToText([Phone Number]);
  
  var text RemoveBracketOne = SearchAndReplace($Tel,"(","");
  var text RemoveBracketTwo = SearchAndReplace($RemoveBracketOne,")","");
  var text RemoveHyphen = SearchAndReplace($RemoveBracketTwo,"-","");
  
  $RemoveHyphen
  
  ------------------------------
  Jessica Matenga
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    2 years ago
    The spacing in my original post was odd, but you need the last line as well.
    
    var text Tel = ToText([Phone Number]);
    
    var text RemoveBracketOne = SearchAndReplace($Tel,"(","");
    var text RemoveBracketTwo = SearchAndReplace($RemoveBracketOne,")","");
    var text RemoveHyphen = SearchAndReplace($RemoveBracketTwo,"-","");
    SearchAndReplace($RemoveHyphen," ","")
    
    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------