Formatting Phone Number in formula

[CJB]

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?

[Yasutaka_Ito · Accepted solution]

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

[Justin_Barrett]

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}, "[+ -.]", ""),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)