What does Validity consider a suffix?

[CRMSarah]

According to Google, suffixes can include professional designations like CPA, PMP, Esq., etc. I was hoping to use the formula ParseNameReturnSuffix(${Name}) to extract these designations from the Last Name field and place them in the Suffix field.

Can someone confirm whether professional designations are actually included in the criteria for suffix?

[AnthonyValidity]

Hi @CRMSarah !

I tested CPA, PMP, ESQ and they all worked as expected with the formula.  It doesn't capture all suffixes in all sectors, but has the most common.  I'm going to see if I can get a list.  I also was able to create a custom formula if you find that the preinstalled formula is not capturing all the ones you want.

IF(
  OR(
    LOWER(RIGHT(TRIM(${Name}), 3)) = " jr",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " jr.",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " sr",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " sr.",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " ii",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " iii",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " iv",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " md",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " phd",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " jd",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " jd.",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " dds",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " edd",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " mba",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " cpa",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " cfa",
    LOWER(RIGHT(TRIM(${Name}), 5)) = " cissp",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " pmp",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " cfp",
    LOWER(RIGHT(TRIM(${Name}), 4)) = " esq",
    LOWER(RIGHT(TRIM(${Name}), 5)) = " esq.",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " pe",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " rn",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " np",
    LOWER(RIGHT(TRIM(${Name}), 5)) = " arnp",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " do",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " pa",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " ma",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " ms",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " ba",
    LOWER(RIGHT(TRIM(${Name}), 3)) = " bs"
  ),
  IF(
    OR(LOWER(RIGHT(TRIM(${Name}), 3)) = " jr", LOWER(RIGHT(TRIM(${Name}), 4)) = " jr."),
    "Jr.",
    IF(
      OR(LOWER(RIGHT(TRIM(${Name}), 3)) = " sr", LOWER(RIGHT(TRIM(${Name}), 4)) = " sr."),
      "Sr.",
      IF(
        LOWER(RIGHT(TRIM(${Name}), 3)) = " ii",
        "II",
        IF(
          LOWER(RIGHT(TRIM(${Name}), 4)) = " iii",
          "III",
          IF(
            LOWER(RIGHT(TRIM(${Name}), 3)) = " iv",
            "IV",
            IF(
              LOWER(RIGHT(TRIM(${Name}), 4)) = " phd",
              "PhD",
              IF(
                LOWER(RIGHT(TRIM(${Name}), 3)) = " md",
                "MD",
                IF(
                  OR(LOWER(RIGHT(TRIM(${Name}), 3)) = " jd", LOWER(RIGHT(TRIM(${Name}), 4)) = " jd."),
                  "JD",
                  IF(
                    LOWER(RIGHT(TRIM(${Name}), 4)) = " dds",
                    "DDS",
                    IF(
                      LOWER(RIGHT(TRIM(${Name}), 4)) = " edd",
                      "EdD",
                      IF(
                        LOWER(RIGHT(TRIM(${Name}), 4)) = " mba",
                        "MBA",
                        IF(
                          LOWER(RIGHT(TRIM(${Name}), 4)) = " cpa",
                          "CPA",
                          IF(
                            LOWER(RIGHT(TRIM(${Name}), 4)) = " cfa",
                            "CFA",
                            IF(
                              LOWER(RIGHT(TRIM(${Name}), 5)) = " cissp",
                              "CISSP",
                              IF(
                                LOWER(RIGHT(TRIM(${Name}), 4)) = " pmp",
                                "PMP",
                                IF(
                                  LOWER(RIGHT(TRIM(${Name}), 4)) = " cfp",
                                  "CFP",
                                  IF(
                                    OR(LOWER(RIGHT(TRIM(${Name}), 4)) = " esq", LOWER(RIGHT(TRIM(${Name}), 5)) = " esq."),
                                    "Esq.",
                                    IF(
                                      LOWER(RIGHT(TRIM(${Name}), 3)) = " pe",
                                      "PE",
                                      IF(
                                        LOWER(RIGHT(TRIM(${Name}), 3)) = " rn",
                                        "RN",
                                        IF(
                                          LOWER(RIGHT(TRIM(${Name}), 3)) = " np",
                                          "NP",
                                          IF(
                                            LOWER(RIGHT(TRIM(${Name}), 5)) = " arnp",
                                            "ARNP",
                                            IF(
                                              LOWER(RIGHT(TRIM(${Name}), 3)) = " do",
                                              "DO",
                                              IF(
                                                LOWER(RIGHT(TRIM(${Name}), 3)) = " pa",
                                                "PA",
                                                IF(
                                                  LOWER(RIGHT(TRIM(${Name}), 3)) = " ma",
                                                  "MA",
                                                  IF(
                                                    LOWER(RIGHT(TRIM(${Name}), 3)) = " ms",
                                                    "MS",
                                                    IF(
                                                      LOWER(RIGHT(TRIM(${Name}), 3)) = " ba",
                                                      "BA",
                                                      IF(
                                                        LOWER(RIGHT(TRIM(${Name}), 3)) = " bs",
                                                        "BS",
                                                        ""
                                                      )
                                                    )
                                                  )
                                                )
                                              )
                                            )
                                          )
                                        )
                                      )
                                    )
                                  )
                                )
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ),
  ${Suffix}
)

Best,

[CRMSarah]

Holy cow - Thanks Anthony! Can you test the formula provided through DemandTools (ParseNameReturnSuffix(${Name})) on the last name "McPadden"? 😉

[AnthonyValidity]

Hi Sarah,

My pleasure!  I just tested (ParseNameReturnSuffix(${Name})) and it returned nothing into the suffix field.  Are you seeing something different?

Best,

[AnthonyValidity]

Hey @CRMSarah ,

I like this formula better by the way if you're going to be using a custom one:

IF(
  ${Suffix} <> "",
  ${Suffix},
  IF(
    RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " jr",
    "Jr.",
    IF(
      RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " sr",
      "Sr.",
      IF(
        RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " ii",
        "II",
        IF(
          RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " iii",
          "III",
          IF(
            RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " iv",
            "IV",
            IF(
              RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " phd",
              "PhD",
              IF(
                RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " md",
                "MD",
                IF(
                  RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 3) = " jd",
                  "JD",
                  IF(
                    RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " dds",
                    "DDS",
                    IF(
                      RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " edd",
                      "EdD",
                      IF(
                        RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " mba",
                        "MBA",
                        IF(
                          RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " cpa",
                          "CPA",
                          IF(
                            RIGHT(LOWER(TRIM(RegExReplace(${Name}, "[^a-zA-Z ]", ""))), 4) = " pmp",
                            "PMP",
                            ""
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

This one will also strip special characters while checking for a suffix.  For instance, J-R will become Jr.  I condensed it a bit too.

Thanks,