Skip to main content

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)

or

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.

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:


LEFT(

    Names,

    {Space 1} - 1

)

& IF(

    {Space 2},

    " & " &

    MID(

        Names,

        {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.

Reply


Terms & Conditions