Hello All. In this tutorial, let’s look at how to extract the last name from a name with middle name. The formula can be complex and I will break the formula in to smaller steps so that you can follow every element of the formula used. Let’s look at the dataset and the formula
Formula: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))=1,RIGHT(A2,LEN(A2)-FIND(” “,A2)),MID(A2,FIND(” “,A2,FIND(” “,A2,1)+1),100))
Logically the formula first checks how many spaces the name has and accordingly returns the value. Let’s break the entire formula into 3 parts
Part 1 – Return the last name only with one space (without middle name)
RIGHT(A2,LEN(A2)-FIND(” “,A2)) : We use the “Right” function that would return the specific number of characters from the end. Here in this formula, the function first checks where the space is, take that as a value to be subtracted from the total length of the text string. This is the character number from where the right function should return. The above formula would return Ramesh Tendulkar, Dravid, Chandidas Ganguly, Kohli respectively
Part 2 – MID function to increment spaces and return last name
MID(A2,FIND(” “,A2,FIND(” “,A2)+1),100): We use the “MID” that would return the middle of the string when the starting position and the total length of characters is specified. The “Find” function helps to return the position of the specified string. Here in this formula, the function uses the “Find” function to check where the space is – optionally the start number to start the search for the “space” is given as another “find” function. This part may be confusing, let’s follow the actual steps:
Find(” “,A2) – Return the position of the SPACE in A2
Find(” “,A2),Find(” “A2)+1 – Return the position of the SPACE after the first space (as we are incrementing by 1) in A2
So, the function has found where the 2nd space is in the text string. MID function will now return the value post the second space and the length would be upto 100 characters. If the 100 was 1, it would only return “T”.
Part 3: Combine Part 1 and 2 with logical check
Now, let’s combine both part 1 and part 2 with a logical “If” function to check how many spaces (a.k.a if there is a middle name in input or not) are there and return the name accordingly.
I know this was technically deep and might sound confusing – but logically very simple. First, calculate where the space is and return the rest of values. 2nd increment the space by 1 and return the rest of values.
I hope you like this tutorial. Please let me know your feedback that would help to improve the content. Thanks for reading.