| 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 persons 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 | | | | | |
|
|
|
|
|
|
|
|
|
| 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)) |
