Extracting 1st & 3rd word from 4 word field

Topic Labels: Formulas
5366 2
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I have a lookup field. Sometimes it has one name in it:
John Doe

Sometimes it has two names in it:
John Doe Jane Smith

It will never have more than four words. I want to use a formula to output their first names only, so that the new field reads:
John (if only one person)
John & Jane (if two people).

Is this at all possible? I know you can do LEFT({Names},SEARCH(" ",{Names})-1) to get John, but I’m not sure how to tweak it to get John & Jane.

2 Replies 2
8 - Airtable Astronomer
8 - Airtable Astronomer

One way is to nest multiple searches and use an If statement(s). Use the position of the previous " " as the starting point for the subsequent search/find.
In the IF statement, if no 2nd space is found, don’t search for a 2nd name.

for example, this should return both the first and last name,

LEFT({Names},SEARCH(" “,{Names},SEARCH(” ",{Names}))-1)

I tried Mike’s formula, but it only returned the first name in both situations, so I dove in a had a play on my own. At first I tried throwing everything into a single formula, but things just got really hairy really quickly. In a case like this where extracting specific chunks of text involves referencing the same points multiple times, I decided to create helper formula fields to find those points, and then use those as pseudo-variables in the main formula.

For this, I made three helper formula fields — {Space 1}, {Space 2}, and {Space 3} — using the following formulas, respectively:

FIND(" ", Names)

FIND(" ", Names, FIND(" ", Names) + 1)

FIND(" ", Names, FIND(" ", Names, FIND(" ", Names) + 1) + 1)

With that done, the main formula looks like this:

    {Space 1} - 1
& IF(
    {Space 2},
    " & " &
        {Space 2} + 1,
        {Space 3} - {Space 2}

If you want, you can obviously make one massive formula, replacing the field names with their respective formulas. Your call.