In the current version of Excel this is a fairly simple problem using the TEXTAFTER and TEXTBEFORE functions.
Both approaches are explained below.
A positive instance number tells TEXTBEFORE to count from thestartof the text string.
A negative instance number tells TEXTBEFORE to count from theend.
Next, we need to calculate the number of characters to extract.
This is a more difficult problem.
The formula above will work correctly when there are two spaces in the name.
However, when a second space is not found (i.e.
there is no middle name, FIND will return a #VALUE!
For example, =MID(“apple”,2,3) returns “ppl”.
When the text is not found, FIND returns a #VALUE error.
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
Related videos
How to create a complex formula step by step
