Extract First , Middle and Last Name

Formula for First Name : -
=LEFT(A2,FIND(" ",A2)-1)

Formula for Middle Name :-
=MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))

Formula for Last Name :-
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Understanding :-
First Name : First Name starts from the first letter and end till the first blank. So, our first purpose is to find the first blank, which is done from FIND function. FIND function helps you to find the first blank , in example "B. Kumar Bansal", FIND function gives you the result 3 as the first blank is in position 3. Therefore, just before first blank, we have our required word i.e First Name, So,if we subtract 1 from FIND function result (as we have to remove the blank which takes 1 word), we get the total length of first word (i.e 3-1=2). Use LEFT function , as it looks the data from left (starting) of A2 i.e B. Kumar Bansal till the length reached before the first blank i.e 2 (=3-1) .

Middle Name : Middle Name starts after the first blank and end before the last blank. So, we need to find the first blank and the last blank. First blank will be find as like we did in First Names , but since it starts after the blank so we add 1 to it i.e FIND(" ",A2)+1 .Notice that after the first blank it can also be Last name as Middle name not necessarily exists. More than one blank means middle name exists. Before the procedure, we first need to count the difference of length with or without blank, as the difference will be the number of blanks. Total length with blank obviously can be found from LEN function and total length without blank can be found by converting the data without blank ( from SUBSTITUTE function ) and then count the length.

Total No. of  blanks in data :-

LEN(A2)-LEN(SUBSTITUTE(A2," ","")

Now, we need to find the last blank position, for that use FIND function as we know the problem that FIND function brings the position of first blank. To solve it we need to SUBSTITUTE the last blank with let say "*", and find the position of  "*" which is in place of last blank . To substitute the last blank with "*" use

SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

as, LEN(A2)-LEN(SUBSTITUTE(A2," ","") is the total number of blanks which can be use as [instance_num] to place "*" in the last blank .


Now, use FIND function to find the "*" .

FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Above formula brings the total number of words till the last blanks (i.e the combine length of first and middle name).

Since we only need to find the MIDDLE names, we have to subtract the length of first names also. For that, use
FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))
It brings the total length of middle name.

Now, use MID function to extract the middle names ,syntax:
=MID (text , start_num, num_chars)

which is,
=MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))

Last Names :
Last name starts after the last blank.

To find the position of last blank as we did before in Middle name
FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

As above formula brings the total length till last blank , means the length of all except the last name.
So, if we subtract the total length with above we get the length of last name
i.e LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Lastly, use RIGHT function, syntax:
RIGHT(text, [num_chars])

i.e
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))