[excel] 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

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)

Try this:

Right(RC[-1],Len(RC[-1])-InStrRev(RC[-1]," "))

Try this:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(" ",TRIM(A2)))

I was able to copy/paste the formula and it worked fine.

Here is a list of Excel text functions (which worked in May 2011, and but is subject to being broken the next time Microsoft changes their website). :-(

You can use a multiple-stage-nested IF() functions to handle middle names or initials, titles, etc. if you expect them. Excel formulas do not support looping, so there are some limits to what you can do.

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

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.

Right(A1, Len(A1)-Find("(asterisk)",Substitute(A1, "(space)","(asterisk)",Len(A1)-Len(Substitute(A1,"(space)", "(no space)")))))

Try this. Hope it works.

RIGHT return whatever number of characters in the second parameter from the right of the first parameter. So, you want the total length of your column A - subtract the index. which is therefore:

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

And you should consider using TRIM(A2) everywhere it appears...

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!