Apr 10, 2019 02:40 PM
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.
Apr 10, 2019 05:54 PM
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)
Apr 10, 2019 08:30 PM
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.