Lets start with D1 (IMHO the easiest
)
LEFT(A1,FIND(",",A1)-1)
The text in A1 is Smith, Fred Mr
Take the inner function - FIND(",",A1) - This will find the position of "," in A1 => Smith
, => 6
We then subtract 1 from this => 5
So the main formula becomes
LEFT(A1,5) => return 5 characters from A1 starting from the left => Smith
Now the formula in B1
TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
This is a very common method to extract the last word in a string.
Take the inner function => REPT(" ",255) => This just returns a string of 255 spaces.
SUBSTITUTE(A1," ",string of 255 spaces) => replaces all spaces in the text with 255 spaces
RIGHT(string with all spaces replaced by 255 spaces,255) => returns a string of 255 characters starting from the right => includes the last word in the text + a whole lot of spaces
TRIM(last word in the text + a whole lot of spaces) => last word in text => Mr
Now we move to C1
MID(A1,FIND(",",A1)+2,FIND(" ",A1,FIND(",",A1)+2)-FIND(" ",A1)-2)
The syntax for the MID function is MID(Text, Start Number, Num of Characters)
lets look at start number
FIND(",",A1)+2 => This finds the position of "," in the text => 6 as established earlier => 6+2 => 8
If we do a MID(A1,6,1) we will get "F"
lets look at num of characters
FIND(" ",A1,FIND(",",A1)+2)-FIND(" ",A1)-2
The syntax of FIND is FIND(Text, Within Text, [Start Number]) => Start Number is optional and by default is 1. So you can leave it blank and it'll assume 1 i.e. start from the beginning. However you can specify the start number so that FIND will start looking after the number specified. This is especially useful if you have text with 2 spaces and you want the position of the 2nd space.... so you have FIND(" ",A1,Position of 1st space + 1) which will begin FIND after the 1st space => the 2nd space.... coming back to our problem
FIND(" ",A1,
FIND(",",A1)+2)-FIND(" ",A1)-2
=> FIND(" ",A1,8)-FIND(" ",A1)-2
=> 12
-FIND(" ",A1)-2
If you leave out the red part you get MID(A1,8,12) => return text that is 12 characters long starting from the 8th character => Fred Mr => incorrect.
To get correct results you need to subtract the position of the 1st space from the position of the 2nd space
(I have replaced space with @ below to make it easier to understand)
Smith,@Fred@Mr => 12
Smith,@Fred@Mr => 8
12 - 8 = 4
=> 12
-FIND(" ",A1)-2
=> 12
-6-2
=> 12
-8
=> 4
So the initial formula becomes
MID(A1,8,4) => Return a text that is 4 characters long starting from the 8th character => Fred
Hope this helps!
Also note that the formula in C1 will work only if there is one word between the "," and Mr (of course it can be extended to include 2-3-4 etc words, but that is where the pattern/format of text comes in.... which is what I was asking in the beginning)
