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
Solved
Formula to convert E164 to US phone number
+17Best answer by Zack_S
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:
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:
+17Formula 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) )
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) )
This formula isn’t compatible with a E164 format as input.
+17This formula isn’t compatible with a E164 format as input.
What field types are you using?
The direct copy and paste worked for me.
+17What field types are you using?
The direct copy and paste worked for me.
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:
+17Using
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:
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:
+17You’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) )
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.