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
alexdev1.3920490842142832E12alexdev1.3920490842142832E12 

Workflow rule to auto-format phone number

I would like to make sure all phone numbers are updated to conform to this format:

(xxx) xxx-xxxx

I tried to write a workflow rule to address this as follows:

NOT (
   REGEX( HomePhone , "([0-9]{3})[0-9]{3}-[0-9]{3}")
)

However, I get the following syntax error: "Error: Function REGEX may not be used in this type of formula"
Is there a way to use REGEX here?  Is there a better way to go about this?

I
Satish_SFDCSatish_SFDC
You can try changing the Data type of the field to Phone and the data will be automatically formatted for all the fields.
However, you have to try this on your demo org first.
Once changed, you can fire an update using Dataloader and all the records should be automatically formatted.

Regards,
Satish Kumar
alexdev1.3920490842142832E12alexdev1.3920490842142832E12
The data type is already 'phone'. But I'm getting non-formatted data when it comes in via a web form that uses the API. I do have validation on the form, but it fails periodically and I'd like a backup.
Elie.RodrigueElie.Rodrigue
The regex formula can only be used in validation rules.
However, i'm pretty sure that its not what you want as this would stop anything from being created if the phone format is not the same as yours.

You can create the workflow you want using the left, right and isnumber methods :

not(and(
len(HomePhone) == 14,
left(HomePhone,1) == '(',
isnumber(left(right(HomePhone,13),3)),
left(right(HomePhone,10),1) == ')',
left(right(HomePhone,9),1) == ' ',
isnumber(left(right(HomePhone,8),3)),
left(right(HomePhone,5),1) == '-',
isnumber(right(HomePhone,4))
))

this is looking for (111) 111-1111

Dont forget to mark as best answer if it solve your issue!
Heidi G.Heidi G.
Hi team, I get the following error:  Error: Formula result is data type (Boolean), incompatible with expected data type (Phone).
Jim ScottJim Scott
I use a rule like the above that looks for any number not correctly formatted that contains either 10 numerals or 11 with the first numeral being "1".

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)
)
 
Bruce Teichman 1Bruce Teichman 1
Jim, the solution above works perfectly, thank you!!
Jack Stephenson 1Jack Stephenson 1
Is there any way to delete the +1 for numbers as well?
 
Corey RitterCorey Ritter
Is this a Workflow that Jim created?
MoazzamKhanMoazzamKhan
Thank you @jim
Nelson GarciaNelson Garcia
Hi, I have the same problem but in addition I have different number of digits, what should i do?
Sudheer A 4Sudheer A 4
Hi I have Identified following Workflow rule to Auto format the phone number to Australian phone number  (+61 XXX XXX XXX)for following scenarios:(this will handle the scenario where the 10 digit number is saved as (0XX) XXX-XXXX.
4XXXXXXXX -- +61 XXX XXX XXX
04XXXXXXXX  - +61 XXX XXX XXX
+614XXXXXXXX - +61 XXX XXX XXX
(04X)XXXXXXX - +61 XXX XXX XXX
(61)4XXXXXXXX - +61 XXX XXX XXX
(+61)4XXXXXXXX - +61 XXX XXX XXX
+61-4XXXXXXXX - +61 XXX XXX XXX
61-4XXXXXXXX - +61 XXX XXX XXX

IF(Len(MobilePhone) == 9, "+61" & " " & MID(MobilePhone,1,3) & " " & MID(MobilePhone,4,3) & " " & MID(MobilePhone,7,3), 
IF(AND(Len(MobilePhone) == 10,Left(MobilePhone,1)=="0"), "+61" & " " & MID(MobilePhone,2,3) & " " & MID(MobilePhone,5,3) & " " & MID(MobilePhone,8,3), 
IF(AND(Len(MobilePhone) == 14,LEFT(MobilePhone,2)=="(0"), "+61" & " " & MID(MobilePhone,3,2) & MID(MobilePhone,7,1) & " " & MID(MobilePhone,8,2) & MID(MobilePhone,11,1) & " " & RIGHT(MobilePhone,3),  
IF(AND(Len(MobilePhone) == 12, Left(MobilePhone,1)=="+"), "+61" & " "& MID(MobilePhone,4,3) & " " & MID(MobilePhone,7,3) & " " & MID(MobilePhone,10,3),  
IF(AND(Len(MobilePhone) == 13,LEFT(MobilePhone,4)=="(61)"), "+61" & " "& MID(MobilePhone,5,3) & " " & " "& MID(MobilePhone,8,3) & " " & MID(MobilePhone,11,3),  
IF(AND(Len(MobilePhone) == 14,LEFT(MobilePhone,5)=="(+61)"), "+61" & " "& MID(MobilePhone,6,3) & " " & " "& MID(MobilePhone,9,3) & " " & MID(MobilePhone,12,3),  
IF(AND(Len(MobilePhone) == 13,LEFT(MobilePhone,4)=="+61-"), "+61" & " "& MID(MobilePhone,5,3) & " " & " "& MID(MobilePhone,8,3) & " " & MID(MobilePhone,11,3),  
IF(AND(Len(MobilePhone) == 12,LEFT(MobilePhone,3)=="61-"), "+61" & " "& MID(MobilePhone,4,3) & " " & " "& MID(MobilePhone,7,3) & " " & MID(MobilePhone,10,3), MobilePhone))   
 ) ) ) ) ) )

if you don't want spaces just remove them from the formula.


If the above format has some spaces to handle then we just have to replace 'MobilePhone' with SUBSTITUTE(MobilePhone , " ", '') except the last one.

the whole formula looks like 

IF(Len(SUBSTITUTE(MobilePhone , " ", '')) == 9, "+61" & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),1,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),4,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),7,3), 
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 10,Left(SUBSTITUTE(MobilePhone , " ", ''),1)=="0"), "+61" & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),2,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),5,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),8,3), 
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 14,LEFT(SUBSTITUTE(MobilePhone , " ", ''),2)=="(0"), "+61" & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),3,2) & MID(SUBSTITUTE(MobilePhone , " ", ''),7,1) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),8,2) & MID(SUBSTITUTE(MobilePhone , " ", ''),11,1) & " " & RIGHT(SUBSTITUTE(MobilePhone , " ", ''),3),  
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 12, Left(SUBSTITUTE(MobilePhone , " ", ''),1)=="+"), "+61" & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),4,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),7,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),10,3),  
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 13,LEFT(SUBSTITUTE(MobilePhone , " ", ''),4)=="(61)"), "+61" & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),5,3) & " " & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),8,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),11,3),  
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 14,LEFT(SUBSTITUTE(MobilePhone , " ", ''),5)=="(+61)"), "+61" & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),6,3) & " " & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),9,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),12,3),  
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 13,LEFT(SUBSTITUTE(MobilePhone , " ", ''),4)=="+61-"), "+61" & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),5,3) & " " & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),8,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),11,3),  
IF(AND(Len(SUBSTITUTE(MobilePhone , " ", '')) == 12,LEFT(SUBSTITUTE(MobilePhone , " ", ''),3)=="61-"), "+61" & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),4,3) & " " & " "& MID(SUBSTITUTE(MobilePhone , " ", ''),7,3) & " " & MID(SUBSTITUTE(MobilePhone , " ", ''),10,3), MobilePhone))   
 ) ) ) ) ) )
Anmol PanchalAnmol Panchal
Hey @Jim How to remove / from the phone field automatically to store in us phone format. I made few changes to your code but it doent work as i want.

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





#############################################################################################################################



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(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+","")),
   OR(
    LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""))= 11,
    AND(
      LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""))=11,
      LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Phone , ".", ''),"/", ""),"-",""),"+",""),1)="1"
    )
  )
) 
Neeta Jain 7Neeta Jain 7
IRequirement:  have a phone field with value: (+44) 7556352881:United Kingdom

What I did: have created WF field update with frmula below. but the united kindgom is not going away. can you pls help what needs to be included: 

Result only showing phone number and string: SUBSTITUTE( SUBSTITUTE( SUBSTITUTE  (SUBSTITUTE(SUBSTITUTE ( SUBSTITUTE( SUBSTITUTE( Phone, '-', ''), '(', ''), ')', ''), ' ', ''),'.', ''), '+', ''), ':', '')