
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 22, 2021 11:29 AM
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?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 22, 2021 07:44 PM
@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.
- I’m stripping away any unwanted characters from the {Phone Number} field.
REGEX_REPLACE({Phone Number}, "[+ -.]", "")
- 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)
- 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 22, 2021 07:44 PM
@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.
- I’m stripping away any unwanted characters from the {Phone Number} field.
REGEX_REPLACE({Phone Number}, "[+ -.]", "")
- 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)
- 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 22, 2021 11:21 PM
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)
