If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts of the name.
In the example, the active cell contains this formula:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
How the formula works:
At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters need to be extracted.
At a high level, the formula replaces the last space in the name with an asterisk “*” and then uses FIND to determine the position of the asterisk in the name. The position is used to work out how many characters to extract with RIGHT.
How does the function replace only the last space? This is the clever part.
Buckle up, the explanation gets a bit technical.
They key to this formula is this bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Which does the actual replacement of the last space with “*”.
SUBSTITUTE has a forth (optional) argument that specifies which “instance” of the find text should be replaced. If nothing is supplied for this argument, all instances are replaced. However, if, say the number 2 is supplied, only the second instance is replaced. In the snippet above, instance is calculated using the second SUBSTITUTE:
Here, the length of the name without any spaces is subtracted from the actual length of the name. If there’s only one space in the name, it produces 1. If there are two spaces, it the result is 2, and so on.
In the example name in B4, there is one space in the name, so we get:
16 – 15 = 1
And one is used as in the instance number:
which replaces the second space with “*”. The name then looks like this:
The FIND function then takes over to figure out where the “*” is in the name:
The result is 8 (the * is in the 8th position) which is subtracted from the total length of the name:
Since the name is 16 characters, we have:
16-8 = 8
The number 8 is used by RIGHT like so:
Which results in “Johanson”
As, you can see, it’s a lot of work above to calculate that simple 8!