Formula to convert E164 to US phone number

[Ambroise_Dhenai]

I have the following phone number +33661291700, do you know a formula that converts it to a US phone number? (e.g: (415) 555-2671)

Thank you

[Zack_S]

Formula is here

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) )

Formatting Phone Number in formula Formulas

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 t…

[Ambroise_Dhenai]

This formula isn’t compatible with a E164 format as input.

[Zack_S]

What field types are you using?

The direct copy and paste worked for me.

[Ambroise_Dhenai]

Using

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

Getting #ERROR! for all, even when valid.

My studentPhoneNumberAsE164 field is a lookup field from a formula (text) field, not a native Phone number field, may that be why it fails? :thinking:

[Zack_S · Accepted solution]

I believe it’s because a lookup field is a list (array), should be able to convert this to a string if there is only one number in it.

Change each studentPhoneNumberAsE164 to CONCATENATE(studentPhoneNumberAsE164)

:crossed_fingers:

[Ambroise_Dhenai]

You’re right. It worked with ARRAYJOIN.

CONCATENATE( "(", MID(RIGHT(REGEX_REPLACE(ARRAYJOIN(studentPhoneNumberAsE164), "[+ -.]", ""),10), 1,3), ") ", MID(RIGHT(REGEX_REPLACE(ARRAYJOIN(studentPhoneNumberAsE164), "[+ -.]", ""),10), 4,3), "-", MID(RIGHT(REGEX_REPLACE(ARRAYJOIN(studentPhoneNumberAsE164), "[+ -.]", ""),10), 7,4) )