Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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.
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.
REGEX_REPLACE({Phone Number}, "[+ -.]", "")
RIGHT(REGEX_REPLACE({Phone Number}, "[+ -.]", ""), 10)
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.
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.
REGEX_REPLACE({Phone Number}, "[+ -.]", "")
RIGHT(REGEX_REPLACE({Phone Number}, "[+ -.]", ""), 10)
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.
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)