Extract the last substring from a cell

I have names in a column. I need to split just the last names from that column into another column.

The last name is delimited by a space from the right side.

The contents in cell A2 = Alistair Stevens and I entered the formula in cell B2 (I need 'Stevens' in cell B2)

I tried using the following formulas:

=RIGHT(A2,FIND(" ",A2,1)-1)

=RIGHT(A2,FIND(" ",A2))

Both these formulas work for this cell but when I fill it down / copy and paste it for the cells below it doesn't work. I get the wrong values!!

A3 -> David Mckenzie

B3 -> Mckenzie

Solution 1:

This works, even when there are middle names:

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,LEN(A2))

If you want everything BUT the last name, check out this answer.

If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula.

Note that it is only by pure chance that your first formula =RIGHT(A2,FIND(" ",A2,1)-1) kind of works for Alistair Stevens. This is because "Alistair" and " Stevens" happen to contain the same number of characters (if you count the leading space in " Stevens").

Solution 2:

The answer provided by @Jean provides a working but obscure solution (although it doesn't handle trailing spaces)

As an alternative consider a vba user defined function (UDF)

Function RightWord(r As Range) As Variant
    Dim s As String
    s = Trim(r.Value)
    RightWord = Mid(s, InStrRev(s, " ") + 1)
End Function

Use in sheet as
=RightWord(A2)

Solution 3:

Try this function in Excel:

Public Shared Function SPLITTEXT(Text As String, SplitAt As String, ReturnZeroBasedIndex As Integer) As String
        Dim s() As String = Split(Text, SplitAt)
        If ReturnZeroBasedIndex <= s.Count - 1 Then
            Return s(ReturnZeroBasedIndex)
        Else
            Return ""
        End If
    End Function

You use it like this:

First Name (A1) | Last Name (A2)

Value in cell A1 = Michael Zomparelli

I want the last name in column A2.

=SPLITTEXT(A1, " ", 1)

The last param is the zero-based index you want to return. So if you split on the space char then index 0 = Michael and index 1 = Zomparelli

The above function is a .Net function, but can easily be converted to VBA.

Solution 4:

If you want to get the second to last word in a text, you can use this macro as a function in your spreadsheet:

Public Function Get2ndText(S As String) As String

Dim sArr() As String
Dim i As Integer
sArr = Split(S, " ")

'get the next to the last string
i = UBound(sArr) - 1
Get2ndText = sArr(i)

End Function

Then in your spreadsheet B1 as the text:

CURRENT OWNER 915 BROADWAY ST HOUSTON TX 77012-2126

in B2 your formula would be:

=Get2ndText(B1)

The result would be

TX

Solution 5:

Simpler would be: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))

You can use A2 in place of TRIM(A2) if you are sure that your data doesn't contain any unwanted spaces.

Based on concept explained by Rick Rothstein: http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

Sorry for being necroposter!