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!
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
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!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.