Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# Formula to convert E164 to US phone number

Topic Labels: Formulas
Solved
337 6
cancel
Showing results for
Did you mean:
8 - Airtable Astronomer

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

1 Solution

Accepted Solutions
7 - App Architect

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:

6 Replies 6
7 - App Architect

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) )
``````
8 - Airtable Astronomer

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

7 - App Architect

What field types are you using?

The direct copy and paste worked for me.

8 - Airtable Astronomer

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:

7 - App Architect

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:

8 - Airtable Astronomer

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