Split Forename and Surname

Finding the First Name
Full Name First Name
Alan Jones Alan =LEFT(C14,FIND(" ",C14,1))
Bob Smith Bob =LEFT(C15,FIND(" ",C15,1))
Carol Williams Carol =LEFT(C16,FIND(" ",C16,1))
Finding the Last Name

Full Name Last Name
Alan Jones Jones =RIGHT(C22,LEN(C22)-FIND(" ",C22))
Bob Smith Smith =RIGHT(C23,LEN(C23)-FIND(" ",C23))
Carol Williams Williams =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names.
If there is also a middle name, the last name formula will be incorrect.
To solve the problem you have to use a much longer calculation.

Full Name Last Name
Alan David Jones Jones
Bob John Smith Smith
Carol Susan Williams Williams
=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name

Full Name Middle Name
Alan David Jones David
Bob John Smith John
Carol Susan Williams Susan
=LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

Please use this method, if any one has any new idea, comments or queries in this regard, i would request you to post here in the comment section.