Skip to main content
Solved

Extracting text after a comma from a string

  • July 1, 2025
  • 4 replies
  • 73 views
A
Forum|alt.badge.img+6
  • at-com
  • Participating Frequently

I have a name field that sometimes has one name and sometimes has two. If there are two names, they are seperated by a comma.

What is a formula I can use to show only the first names, with an ‘and’ in the middle if there are two.

John Doe                                    John

John Doe, Josh Frank             John and Josh

Best answer by Mike_AutomaticN

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

4 replies

Mike_AutomaticN
Forum|alt.badge.img+28

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

TheTimeSavingCo
Forum|alt.badge.img+31

Can try using regex for this too:

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

 

Adam, Airtable Consultant - thetimesaving.company
A
Forum|alt.badge.img+6
  • at-com
  • Author
  • Participating Frequently
  • July 23, 2025

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?

Mike_AutomaticN
Forum|alt.badge.img+28

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

Terms & ConditionsAccessibility statement