Skip to main content
Solved

Formatting Phone Number in formula

  • July 22, 2021
  • 2 replies
  • 152 views
C
Forum|alt.badge.img+5
  • CJB
  • New Participant

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?

Best answer by Yasutaka_Ito

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

2 replies

Y
Forum|alt.badge.img+4
  • Yasutaka_Ito
  • New Participant
  • Answer
  • July 23, 2021

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

Soly
C
C
Justin_Barrett
Forum|alt.badge.img+21

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)
C
B
P
Terms & ConditionsAccessibility statement