Assume you have below data and you need to get the last name as Surname & the rest as Other Names.

If only One name exists extract it as the Surname.

Here are the Steps to follow:

1. Find the position of the last word in the name.
   • To do this you need to count the number of spaces in each Full Name. The surname usually follow the last space
     • =LEN([@[FULL NAME]]) – LEN( SUBSTITUTE( [@[FULL NAME]], ” ” ,””))
   • LEN( SUBSTITUTE( [@[FULL NAME]], ” ” ,””)) —Returns the length of the FULL NAME without Spaces
   • LEN([@[FULL NAME]]) —Returns the length of the FULL NAME with Spaces
   • The subtraction between the two returns the total number of spaces
   • NB: Watch out for all leading, trailing, and in-between spaces. You should only count a single space character between words.

• 2. Substitute the Last space with an asterisk (*). This will help in finding its position in the Word.
  • =SUBSTITUTE([@[FULL NAME]],” “,”*”,LEN([@[FULL NAME]])-LEN(SUBSTITUTE([@[FULL NAME]],” “,””)))
  • NB: Please note the #VALUE error on the names without spaces. You will require to use the IFERROR formula later to handle this.

• 3. Now use the FIND function to get the position of this asterisk (*)
  • =FIND(“*”,[@[Substitute Space with *]])

• 4. Now that you asterisk position, you can count the length of the last name. To do this subtract the asterisk position from the length of the full name
  • =LEN([@[FULL NAME]])-[@[Find Position]]

• 5. Use the RIGHT Function to extract the last names now that you know it’s Length.
  • NB: Use the IFERROR function to extract the full name if no spaces are found

=IFERROR(
         RIGHT([@[FULL NAME]],
            LEN([@[FULL NAME]])-
                FIND("",SUBSTITUTE([@[FULL NAME]]," ","",LEN([@[FULL NAME]])-LEN(SUBSTITUTE([@[FULL NAME]]," ",""))))),[@[FULL NAME]])

To extract OTHER NAMES, use the LEFT Function. Extract all data to the left of the asterisk position.

=IFERROR(
     LEFT([@[FULL NAME]],
          FIND("",SUBSTITUTE([@[FULL NAME]]," ","",LEN([@[FULL NAME]])-LEN(SUBSTITUTE([@[FULL NAME]]," ",""))))),"")

Using REPT Function to extract words

After Posting the article on Linkedin, Excel expert Subodh Tiwari pointed an easier way to simplify the above function by use of REPT Function

=TRIM
(
   RIGHT(
     SUBSTITUTE([@[FULL NAME]]," ",REPT(" ",LEN([@[FULL NAME]]))),
        LEN([@[FULL NAME]])) 
)

How does it work

REPT(” “,LEN([@[FULL NAME]]))–takes all single spaces in the FULL NAME and repeats them a number of times equal to the length of the sentence.

SUBSTITUTE([@[FULL NAME]],” “,REPT(” “,LEN([@[FULL NAME]])))–finds the single spaces and replaces them with the repeated spaces from REPT function.

Since you now know the relative position of the spaces, you can use the RIGHT function to find the last word

TRIM Function removes all unnecessary trailing and leading spaces.

Extracting OTHER NAMES except the last one is a bit tricky but all you need to understand is Every part of the Name is a Sentence except the last Part

=TRIM
(
  LEFT(
   SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),
    LEN(B2)*
     (LEN([@[FULL NAME]])-LEN(SUBSTITUTE([@[FULL NAME]]," ",""))))
)

The only trick is knowing how many words to extract, below formula does the count

(LEN([@[FULL NAME]])-LEN(SUBSTITUTE([@[FULL NAME]],” “,””)))… counts the number of words in the name except the last one

Read MORE on REPT Function

Using FLASH FILL to extract the Last Word in Excel

A Quicker way of doing this, but risky and not dynamic, is the use of excel Flash Fill.

NB: Unless you give Excel enough examples, you risk getting wrong results.

Using FILTERXML to extract the Last Word in Excel

Thanks to Chadoo’s article, I learnt how to use FILTERXML to extract the surname & Other names

• The trick lies in converting the FULL NAME into XML
  • Substitute the Spaces in the FULL NAME with a </A><A>
  • SUBSTITUTE([@[FULL NAME]], ” “, “</A><A>”)
  • Create an A node for each word using below formula
  • =”<DATA><A>”& SUBSTITUTE([@[FULL NAME]], ” “, “</A><A>“) & “</A></DATA>”

To extract the Surname , replace the xml with above formula and xpath with //A[last()] in FILTERXML (xml, xpath)

=FILTERXML("<DATA><A>"& SUBSTITUTE([@[FULL NAME]], " ", "</A><A>") & "</A></DATA>", "//A[last()]")

Extracting the Other names, you need TEXTJOIN Function to concatenate the names. Also for the XPath select all other nodes not equal to the last “//A[position()!=last()]“

=TEXTJOIN(" ",,FILTERXML("<DATA><A>"& SUBSTITUTE([@[FULL NAME]], " ", "</A><A>") & "</A></DATA>", "//A[position()!=last()]"))

Above formula will return an Error where there is only 1NAME…So, wrap it with IFERROR

=IFERROR(TEXTJOIN(" ",,FILTERXML(<DATA><A>"& SUBSTITUTE([@[FULL NAME]], " ", "</A><A>") & "</A></DATA>, "//A[position()!=last()]")),"")

Using POWER QUERY to extract the Last Word in Excel

DOWNLOAD WORKSHEET

MORE ON EXCEL FLASH FILL

Excel Flash Fill: 15 Things You Can Do In a Flash. Part 1