VLOOKUP WITH TRIM

In some cases, VLOOKUP returns the wrong results or no results at all. This might not be due to the wrong construction of the VLOOKUP formula but might be caused by leading and trailing spaces in the data. See the example above to understand this phenomenon. See the spaces in column A of the data and notice that the VLOOKUP returns #N/A(Not available error).

Here is what the lookup data looks like

In such an instance, the VLOOKUP fails, to correct this you wrap the TRIM function around the data in column A. See formula below and the results are as expected

You can download the file here

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>