Skip to main content

Hi -


I created a column with an IF statement that pulls phone numbers entered from a survey into the field IF we haven’t recorded the phone number ourselves manually (the survey column is called Phone Number and the manual column is Phone # to differentiate). The formula is


IF({Phone Number}, {Phone Number}, {Phone #})


When the numbers are pulled, some of the formats are in the form that the client filled out XXX.XXX.XXXX but we want it in (XXX) XXX-XXXX. Is there a way to add formatting to the formula to force the phone number to be in the format we want?

@CJB, following formula will do.

CONCATENATE( "(", MID(RIGHT(REGEX_REPLACE({Phone Number}, "[+ -.]", ""),10), 1,3), ") ", MID(RIGHT(REGEX_REPLACE({Phone Number}, "[+ -.]", ""),10), 4,3), "-", MID(RIGHT(REGEX_REPLACE({Phone Number}, "[+ -.]", ""),10), 7,4) )


Here are the explanation.



  1. I’m stripping away any unwanted characters from the {Phone Number} field.

    REGEX_REPLACE({Phone Number}, " + -.]", "")

  2. Am passing the result of #1 into the RIGHT() formula and am taking the 10 digits from the right. This is useful in case someone has entered the country code too while submitting the survey, e.g. +1 123.456.7890

    RIGHT(REGEX_REPLACE({Phone Number}, " + -.]", ""), 10)

  3. I’m using the #2 three times in the CONCATENATE function, each time using the MID function to extract the portion of the digits I want.



  • First time, I’m picking up the first three digits.

  • Second time, I’m picking up the three digits from the 4th position.

  • Third time, I’m picking up the remaining four digits from the 7th position.


Hope this make sense, and solves your purpose.


Wish there was a FORMAT function or something that allowed you to quickly format the input string like we see in Excel.

I’d be tempted to split that into multiple formulas. Because the extraction of the rightmost 10 characters happens multiple times, I’ll split that out into its own formula that I’ll call {Phone Reduced}:


RIGHT(REGEX_REPLACE({Phone Number}, "e+ -.]", ""),10)

Then your main formula can be simplified to this:


"(" & MID({Phone Reduced}, 1, 3) & ") "

& MID({Phone Reduced}, 4, 3) & "-"

& MID({Phone Reduced}, 7, 4)

Reply


Terms & Conditions