You need to sign in to do that
Don't have an account?
alexdev1.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
(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
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
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!
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)
)
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))
) ) ) ) ) )
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"
)
)
)
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, '-', ''), '(', ''), ')', ''), ' ', ''),'.', ''), '+', ''), ':', '')