Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 219
Shyam Natraj Kanagasabapathy Avatar asked May 26 '11 03:05

Shyam Natraj Kanagasabapathy


People also ask

How do I extract the last 3 of a word in Excel?

Select a blank cell, here I select the Cell G1, and type this formula =LEFT(E1,3) (E1 is the cell you want to extract the first 3 characters from), press Enter button, and drag fill handle to the range you want. Then you see the first 3 characters are extracted.


2 Answers

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

like image 62
Jean-François Corbett Avatar answered Oct 05 '22 13:10

Jean-François Corbett


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)

like image 5
chris neilsen Avatar answered Oct 05 '22 15:10

chris neilsen