EXTRACT LAST WORD FROM A CELL

This is all too common in our daily Excel tasks where we need to get the last(rightmost) word from a cell. Let's make it more practical, as an HR Executive in a multinational organisation, you have been given the task of extracting the surnames of all staff members in an excel sheet containing all Employee names.

A pretty easy task if the names are of the form "Firstname Lastname". It becomes more intriguing when some names are of the form earlier mentioned while others are of the form "FirstName MiddleName Surname" or "FirstName Initial Surname".

In the attached excel sheet, you will find 2 formula as well as one UDF(User Defined Function) approach to solving the problem

Here's what Example 1 looks like(And here's the formula that does the extraction)

Extracting Last word from Cell

The UDF is as shown here
<pre>
Function ExtractLastName(cell As Range)
    Dim I As Integer
    If cell.HasFormula = False Then
        If TypeName(Selection) <> "Range" Then
            Exit Function
        Else
            If Len(cell) = 0 Then
                ExtractLastName = ""
            Else
                For I = Len(cell) To 1 Step -1
                    If Mid(cell, I, 1) = " " Then
                        Exit For
                    End If
                Next I
                ExtractLastName = Mid(cell, I + 1, Len(cell) - I)
            End If
        End If
    End If
End Function
</pre>