On Friday, 17 January 2020 03:50:19 UTC, wrote:
This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extract three names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(

Hi
I would use a helper column.
With your data in column A, starting in A2, enter in B2
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
This will tell you how many spaces you have in column A
Then, in C2 enter
=LEFT(A2,FIND(" ",A2&" ")-1) and copy down to give you all first names
In D2 enter
=IF(B2=2,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A2 ," ",CHAR(160),1))-1),"")
and copy down which will extract the middle name, if there is one.
Finally in E2 enter
=IF(B20,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),B2))+1,256),"")
and copy down, which will find the last name, if there is one.

Hope this helps.