Get first names from a name string by removing last name

[James_Samuel]

Imagine a name string: Nina and Ian Fowler
I want to end up with: Nina and Ian by finding and removing: Fowler.

FIND() seems to function left to right, so while I can use:
LEFT(Name,FIND(" ",Name)-1)
To get: Nina

How can I remove the last name: Fowler
To get: Nina and Ian

Any ideas welcome :slightly_smiling_face:

[W_Vann_Hall]

If you’re comfortable that each of your name strings will be in the format ‘Name and Name LastName’, the easiest way is to use SUBSTITUTE() to replace the third space character with a marker character – I typically use the vertical bar (’|’) – and then use LEFT() and FIND() to extract everything up to but not including the marker. This reply includes a formula for doing just that. (Well, it returns everything up to the second hyphen, rather than third space, and it includes an IF() clause to make sure there is a second hyphen, but it should be easily modifiable for your needs.)

That said, there are a number of threads here detailing proper name processing – which can get very tricky. I know I’ve published at least four bases of increasing complexity dealing solely with the proper parsing and formatting of name strings – and I just noticed someone found and corrected a bug in my latest attempt. These should get you started in the right direction, should you need more firepower. (Demo bases are linked from the thread.)

Is there a PROPER name formula in Airtable? Ask the community

Hello all! I’ve searched and have not been able to find a solution for this. I want to be able to reference a column and change all names that appear in one column to PROPER names (which can easily be done in excel) Use Case: Folks are signing up for a service we offer and they input their names in some crazy ways. But we want to convert sloppy username case with Proper case so that the certificates we offer look consistent and classy, instead of having things like: john doe JOHN DOE John …

Extracting last names | last word from a multi-word string Show and tell

The question of how to extract the last name from a combined “first [middle][…] last [suffix]” name string – or the last word from a multi-word string – comes up from time to time. I offer a solution (with caveats) in a reply to a recent support post. My example is name-oriented but could easily be adapted to an arbitrary string. Hope this will save someone some time!

[James_Samuel]

Excellent and very helpful, thank you.

I adapted some of your other helpful code from other links to arrive at this:

LEFT(
Name,
LEN(Name) - LEN(REPLACE({Name},1,FIND("~",SUBSTITUTE({Name}," “,”~",LEN({Name}) - LEN(SUBSTITUTE({Name}," “,”")))),""))
)

[Justin_Barrett]

Just to throw out another option, one trick that I stumbled across a while ago, and which is often helpful when extracting something from the start or end of a string, is to replace an existing separator (like a space) with a massive collection of spaces using REPT. Then I can hack off that piece with a single LEFT or RIGHT using a fixed length matching the size of that space block, and wrap that in TRIM to remove the extra spaces.

For example, if one space becomes 15 spaces, I can safely hack off 15 characters and know that I won’t get any part of the adjacent text. The number depends on the maximum expected size of the piece I want. In this case, it’s probably safe to assume that someone’s surname won’t be longer than 15 characters. So far been a very reliable technique, and often results in a shorter formula than one that relies on finding things in the text.

SUBSTITUTE(Name, " " & TRIM(RIGHT(SUBSTITUTE(Name, " ", REPT(" ", 15)), 15)), "")

[Yonathan_Cohen]

Hi @James_Samuel , I think I have the answer for you … I’m not sure it is the easiest way, but I don’t have time to lose finding more efficient code:

Part 1: extract last name using this formula:

Part 2: extract first name using this formula:

in the part 2, you notice that i copy-pasted the part 1 formula, then removed the space (-1) and I got the first name. It works very well (see below):

https://www.linkedin.com/in/yonathan-cohen-72835a164/