function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Anmol PanchalAnmol Panchal 

Hello I need a help in resolving a small error related to validation of a phone field

I have this code in workflow rule which aims to remove / , . , - , and stores in a proper format (xxx) xxx-xxxx US phone no. But i am facing difficulty in removing / sign by a blank space.

IF(
 LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""))= 11,
 "("&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),2,3)&
 ") "&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),5,3)&
 "-"&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),8,4),
 "("&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),1,3)&
 ") "&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),4,3)&
 "-"&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),7,4)
)
Ajay K DubediAjay K Dubedi
Hi Anmol, 

Below code can fulfill your requirements. Hope this will work for you. 
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,".",''),"-",""),"+",""))=11,
      LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone ,".",''),"-",""),"+",""),1)="1"
    )
  )
)
And here is the formula to use to update the Phone field:
IF(
 LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""))= 11,
 "("&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),2,3)&
 ") "&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),5,3)&
 "-"&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),8,4),
 "("&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),1,3)&
 ") "&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),4,3)&
 "-"&
 MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"-",""),"+",""),7,4)
)

Please mark this as best answer if this solves your problem.

Thank you
Ajay Dubedi