Skip to content

Instantly share code, notes, and snippets.

@cougrimes

cougrimes/standardize_sfdc_phone_formula.txt Secret

Created December 11, 2017 14:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cougrimes/0afe04a1f0f6481730c1f110a11b1d1a to your computer and use it in GitHub Desktop.
Save cougrimes/0afe04a1f0f6481730c1f110a11b1d1a to your computer and use it in GitHub Desktop.
Download ZIP
Standardizes all US phone number formats.
Raw
standardize_sfdc_phone_formula.txt
AND(
NOT(
AND(
LEN(Phone) == 14,
LEFT(Phone,1) == '(',
ISNUMBER(LEFT(RIGHT(Phone,13),3)),
LEFT(RIGHT(Phone,10),1) == ')',
LEFT(RIGHT(Phone,9),1) == ' ',
ISNUMBER(LEFT(RIGHT(Phone,8),3)),
LEFT(RIGHT(Phone,5),1) == '-',
ISNUMBER(RIGHT(Phone,4))
)
),
ISNUMBER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone , ".", ''),"-",""),"+","")),
OR(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone ,".",''),"-",""),"+",""))=10,
AND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone,".",''),"-",""),"+",""))=12,
LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone ,".",''),"-",""),"+",""),1)="+"
)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment