Hey ​@at-com,

If you are always dealing with one or two names (but not more) you can use the following formula:
 

IF(
  FIND(",", {Name}),
  LEFT({Name}, FIND(" ", {Name}) - 1) & " and " &
  MID(
    {Name},
    FIND(",", {Name}) + 2,
    FIND(" ", {Name}, FIND(",", {Name}) + 2) - FIND(",", {Name}) - 2
  ),
  LEFT({Name}, FIND(" ", {Name}) - 1)
)

You can work with such formula structure if you are looking to handle more than 2 names!

Hope this helps.

Mike, Consultant @ Automatic Nation

Can try using regex for this too:

IF(
  FIND(",", {Name}),
  REGEX_EXTRACT({Name}, "^([^ ]+)") & " and " &
  REGEX_EXTRACT({Name}, ", ([^ ]+)"),
  REGEX_EXTRACT({Name}, "^([^ ]+)")
)

Thank you, Mike for that formula. it works perfectly.

I would never have more than two names, but there is one more scenario that I wanted to know if you would be able to include in the formula.

I sometimes have a doctor that I would like to be addressed as Dr. Las Name. For example, Dr. John Doe should show up as Dr. Doe. Is there any way to include that in the same formula?

I’m thinking that I might have to use the regex function in this case, because we can look for the period.

Any ideas?

Took several attempts, but this should hopefully do the trick!
 

IF(
  FIND(",", {Name}),

  IF(
    LOWER(LEFT(TRIM(LEFT({Name}, FIND(",", {Name}) - 1)), 3)) = "dr.",
    "Dr. " &
    TRIM(
      RIGHT(
        LEFT({Name}, FIND(",", {Name}) - 1),
        LEN(LEFT({Name}, FIND(",", {Name}) - 1)) -
        FIND(" ", LEFT({Name}, FIND(",", {Name}) - 1), FIND(" ", LEFT({Name}, FIND(",", {Name}) - 1)) + 1)
      )
    ),
    LEFT(
      TRIM(LEFT({Name}, FIND(",", {Name}) - 1)),
      FIND(" ", TRIM(LEFT({Name}, FIND(",", {Name}) - 1))) - 1
    )
  ) &
  " and " &
  IF(
    LOWER(LEFT(TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name}))), 3)) = "dr.",
    "Dr. " &
    TRIM(
      RIGHT(
        TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name}))),
        LEN(TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name})))) -
        FIND(" ", TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name}))), FIND(" ", TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name})))) + 1)
      )
    ),
    LEFT(
      TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name}))),
      FIND(" ", TRIM(MID({Name}, FIND(",", {Name}) + 1, LEN({Name})))) - 1
    )
  )
)

Please let me know if that is what you are looking for. 

Mike, Consultant @ Automatic Nation