Formula to format Phone Number?

Hi. I am trying to format a phone number column so the data will be easier to read when inserted into a DocuGen document. I have found ways to format dates and numbers but can’t seem to figure out phone numbers. I would like to take the standard 11 digit phone number and format it like below.

14445551212 >> (444) 555-1212

Any help will be greatly appreciated!

@psperry

These examples might get you started. Both basically assume all numbers are US.

The first is simple and will remove a leading “1”, numbers that don’t match the pattern will be returned but not formatted:

REGEXREPLACE({Phone}, "1" & CHAR(123) & "0,1" & CHAR(125) & "(...)(...)(....)", "($1) $2-$3")

The second is more complex formatting longer #s as with extensions, shorter number will not be returned at all:

IF(AND(LEN({Phone})=10,LEFT({Phone}, 1) <> "1"), REGEXREPLACE({Phone}, "(...)(...)(....)","($1) $2-$3"), "") & 

IF(AND(LEN({Phone})=11,LEFT({Phone}, 1) = "1"), REGEXREPLACE({Phone}, "1(...)(...)(....)", "($1) $2-$3"), "") &

IF(AND(LEN({Phone})>10,LEFT({Phone}, 1) <> "1"), REGEXREPLACE({Phone}, "(...)(...)(....)", "($1) $2-$3 x"), "")

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Creating subitems using Make/Integromat The Monday Man (EASY) Way
Contact me directly here: Contact – The Monday Man

2 Likes

Thanks so much for your help! I made an adjustment and am posting it below in case someone else has the same issue. I have three possible phone number formats in my case 4445551212, 14445551212, and +14445551212. Extensions are not currently an issue for us. The change I made:

IF(AND(LEN({Phone})=10,LEFT({Phone}, 1) <> "1"), REGEXREPLACE({Phone}, "(...)(...)(....)","($1) $2-$3"), "") & 

IF(AND(LEN({Phone})=11,LEFT({Phone}, 1) = "1"), REGEXREPLACE({Phone}, "1(...)(...)(....)", "($1) $2-$3"), "") &

IF(AND(LEN({Phone})>11,LEFT({Phone}, 1) <> "1"), REGEXREPLACE({Phone}, "(..)(...)(...)(....)", "($2) $3-$4"), "")

Thanks again!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.