0

As the title states I'm trying to check if my phone numbers are suitable to be converted to standard format (US only). Specifically, I want all 10 digit phone numbers that do not start with 1 and all 11 digit numbers that only start with 1 and are not in standard format already (XXX) XXX-XXXX.

From this page: https://developer.salesforce.com/forums/?id=906F00000009FN3IAM

I have adapted a formula that does most of what I want, but it is still missing "all 11 digit numbers that only start with one". Here is the formula:

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,".",''),"-",""),"+",""))=11,
    AND(
      LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone,".",''),"-",""),"+",""))=10,
      LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone,".",''),"-",""),"+",""),1)!="1"
    )
  )
)

Where Phone is a variable that is assigned to the phone field in the record. This formula will return a boolean that will be used to decide if a second formula should be applied downstream.

1

1 Answer 1

0

You should consider using REGEX for this.

If you've never used it before, it's specifically used to determine format of strings (be it phone number, text, email, etc.)

It would be easier to implement and the hyperlink I've included has examples which would be extremely helpful to get you started.

1
  • Hmm, thank you for this resource. As far as I know, the regex formula they provided doesn't filter out 10-digit phone numbers that start with 1. Admittedly, I don't know REGEX well, but a 10 digit number starting with 1 failed my debug in the flow builder Jun 10 at 16:03

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.