Extract the last substring from a cell

Question

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

Expected result:

B3 -> Mckenzie

Actual result:

B3 -> enzie

Ginsberg

Please post the contents of A3 and the resulting B3 – Naominaor

Answer 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").

Kuska

excel master, thanks Jean-Francois!! works perfectly – Crossbeam

This is awesome! What if the name is Sammy L Davis Jr? How do I get it to ignore Jr, Sr, III, etc.? – Enugu

@bteague Try it out! – Halfbreed

Answer 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)

Slimsy

Obscure, eh? Call me up when Excel allows comments in formulas. – Halfbreed

@Jean: yeah I wish. That would make maintaining my own code soooo much easier – Slimsy

Answer 3

There is now a new excel formula that does this quite easily:

TEXTAFTER(A2," ",-1)

This will return the last name that appears after the last space (-1 instances searches from right to left)

Also very useful for filenames, example

=HYPERLINK(A2,TEXTAFTER(A2,"\",-1))

This will create a link to easily open the file while displaying only the filename if you have the full path in column A

Greenhaw

Answer 4

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!

Boneyard

Answer 5

You can use a combination of the CHOOSECOLS AND TEXTSPLIT functions to get the desired result.¹

A working formula yielding your expected result will be as such:

=CHOOSECOLS(TEXTSPLIT(A2, " "), -1)

Read about the functions here:

• CHOOSECOLS
• TEXTSPLIT

---

¹ Unfortunately, the documentation for the functions does not indicate the version of Excel in which these functions were introduced so it is impossible for me to determine at this time the minimum version of Excel that supports these functions. To be sure you have those functions, you have to be on the latest version of Microsoft Office 365.

Bac

Answer 6

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.

Wallachia

Answer 7

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

Ogee

Thanks Kerry White for your solution. +1 for this. – Bourg

Answer 8

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

Try this. Hope it works.

Oocyte

Answer 9

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...

Misfeasance

this doesn't work when there is a first name, a middle name and a last name. For example if there is a 'John Steven Smith' what I get is 'Steven Smith' – Ginsberg

@Jay He did not. But he asked for "the last substring" – Naominaor

@belisarius -- Ah yes "delimited by a space from the right side". This is tough -- FIND starts at the left side. If there are an unknown number of spaces embedded in the string, he may be out of luck. – Novel

@Jay I am not answering because I don't understand why he is getting "Formula" in B3 ... – Naominaor

@belisarius: cell B3 need to be filled out with a formula, getting the result as a last name. The cell B3 contents is not 'formula'. my apologies for the misunderstanding.. – Ginsberg

Answer 10

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.

Novel

Like @Pok's answer, this does not extract the last substring; rather, it removes the first substring. This is fine when there is only a first name and a last name, but not when there are one or multiple middle names. – Halfbreed

Answer 11

Try this:

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

Headwaiter