Split Forename and Surname in Excel - Easy Excel Tutorial

Split Forename and Surname

The following formula are useful when you have one cell containing text which needs to be split up.

One of the most common examples of this is when a person’s Forename and Surname are entered in full into a cell.

The formula use various text functions to accomplish the task.Each of the techniques uses the space between the names to identify where to split.

Finding the First Name

Finding the Last Name

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.

                                                                               =RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name

                                                                                  =LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))