Get first names from a name string by removing last name

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

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}," “,”")))),""))
)

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)), "")

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/