2

GOAL: I'm trying to create this criteria in Process Builder where I can detect phone number like (000) 000-0000, 000-000-0000 and 000.000.0000 and turn them into +1 0000000000 format.

I tried to write the formula like below and if I input (000) 000-0000 into HomePhone field, that turns into +1 000000000 successfully, but if I tried to update data with 000-000-0000, it would stay 000-000-0000. The same for period format, it stays as 000.000.0000. When I tried those two nested conditions one at a time instead of nesting inside of IF statement, each condition works fine.

Please help me fix the IF statement so that nested conditions get hit when regex returns true.

IF(
REGEX([Contact].HomePhone, "\\D*?(\\d\\D*?){10}"), SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Contact].HomePhone, "(", "+1 "), ") ", ""), "-", ""),
IF(
REGEX([Contact].HomePhone, "^([0-9]{1,3})(?:[0-9\\-]?){4,16}"),
SUBSTITUTE(SUBSTITUTE("+1 " & [Contact].HomePhone, "-", ""), "-", ""),
IF(
REGEX([Contact].HomePhone, "^([0-9]{1,3})(?:[0-9\\.]?){4,16}"),
SUBSTITUTE(SUBSTITUTE("+1 " & [Contact].HomePhone, ".", ""), ".", ""),
[Contact].HomePhone)))

******UPDATES****** Changing my first regex to much more narrowed down test like this REGEX([Contact].HomePhone, "^\\([0-9]{1,3}\\)(?:[0-9\\-]?){4,16}")helped. Thank you Mr David Reed for your help below!

*****Another Solution***** This solution was from the Salesforce Trailblazer Community. fyi https://success.salesforce.com/answers?id=9063A000000a3hMQAQ

IF (NOT(BEGINS([Contact].HomePhone, "+")),
"+1"& SUBSTITUTE(
      SUBSTITUTE(
      SUBSTITUTE(
      SUBSTITUTE([Contact].HomePhone,'-', ''),'(', ''), '), ' ', ''),'.', ''),
        [Contact].HomePhone)
  • Reminder that the syntax is IF(condition, value_if_true, value_if_false). – Adrian Larson Aug 22 '19 at 22:08
  • @AdrianLarson I've seen people nest IF statement and use it as IFs nested inside of else statement. Could you elaborate on what you mean by your comment? Thanks! – il0v3d0g Aug 22 '19 at 22:51
  • in my experience, try vastly simplifying the nested IFs to get something working, then add back in complexity until it breaks – cropredy Aug 23 '19 at 0:31
1

Your first regex is overly broad and matches input that you didn't intend to match, resulting in (a) the SUBSTITUTE() functions not doing what you expect and (b) the further layers of IF() statements not being evaluated.

The regex "\\D*?(\\d\\D*?){10}" (or \D*?(\d\D*?){10}) also matches 000-000-0000 and 000.000.0000, but SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Contact].HomePhone, "(", "+1 "), ") ", ""), "-", "") doesn't work for those formats (since the later arms of this logic are intended to handle those formats).

Consider refining your first regex to specifically match the phone number formats you're handling there by explicitly matching parentheses and dashes. You'll need to escape parentheses if you wish to match them in your regex. Alternately, consider inverting your logic to match the most specific regex first.

| improve this answer | |
  • Awesome! After changing my first regex be more specific, it worked! Thank you so much for your help and great answer! – il0v3d0g Aug 23 '19 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.