Formula to return the last word in a cell

bkaehny

Board Regular
Joined
Jun 11, 2009
Messages
127
Hi all,

I have a column containing full names of people, where each part of their name is separated by a space (e.g. John Smith, John Q Public, Herald van der Schnossen). Some of these names contain middle initials or middle names, some contain just the first and last name, and some even have multiple middle names. I need to separate out just the last name using a formula. Can this be done? I can envision one the uses some iferrors, mids, and finds, but it would quickly become long and cumbersome to adjust. Any ideas?

Thanks.

I'm using Excel 2010, btw.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

Another possible solution based on njimack's example might be:
Excel Workbook
ABC
1John SmithSmithSmith
2John Q PublicPublicPublic
3Herald van der SchnossenSchnossenSchnossen
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B1=MID(A1,MATCH(2,INDEX(1/(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,1000)),1)=" "),0))+1,1000)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note, the second example in column C requires CTRL-SHIFT-ENTER as it is an array formula, the first one doesn't.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi all,

I have a column containing full names of people, where each part of their name is separated by a space (e.g. John Smith, John Q Public, Herald van der Schnossen). Some of these names contain middle initials or middle names, some contain just the first and last name, and some even have multiple middle names. I need to separate out just the last name using a formula. Can this be done? I can envision one the uses some iferrors, mids, and finds, but it would quickly become long and cumbersome to adjust. Any ideas?

Thanks.

I'm using Excel 2010, btw.
If you want the last word regardless if the last name is a multiple word name then try this:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:182px;" /><col style="width:78px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">John Smith</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Smith</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Cher</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Cher</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">John Q Public</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Public</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Herald van der Schnossen</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Schnossen</td></tr></table> <br /><br />
This formula entered in B2 and copied down:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))
 

bkaehny

Board Regular
Joined
Jun 11, 2009
Messages
127
If you want the last word regardless if the last name is a multiple word name then try this:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:182px;" /><col style="width:78px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">John Smith</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Smith</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Cher</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Cher</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">John Q Public</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Public</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Herald van der Schnossen</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Schnossen</td></tr></table> <br /><br />
This formula entered in B2 and copied down:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

Thanks for the additional solutions, people. Can you explain to me why that works, T. Valko?

Edit: I figured it out. That's another ingenius solution. I love how smart the people on this board are.
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Thanks for the additional solutions, people. Can you explain to me why that works, T. Valko?
I have to run to the store to get some ice cream! I'll be back in about an hour!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

If you want the last word regardless if the last name is a multiple word name then try this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 182px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">John Smith</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Smith</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Cher</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Cher</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">John Q Public</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Public</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Herald van der Schnossen</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Schnossen</TD></TR></TBODY></TABLE>


This formula entered in B2 and copied down:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

Thanks for the additional solutions, people. Can you explain to me why that works, T. Valko?

Edit: I figured it out. That's another ingenius solution. I love how smart the people on this board are.
Ok, for those that would like to know how that works...

Let's assume cell A2 = John J. Smith

We use the SUBSTITUTE and REPT functions to replace the single space characters with long strings of space characters. In this case I've used 255 spaces.

The underscores represent a space character. Imagine there are 255 of them between each word in the string. The cell entry will look something like this:

John__________J.__________Smith

Then we use the RIGHT function and extract 255 characters starting from the right-most character. That would look something like this:

_______Smith

Then we use the TRIM function to strip off the leading space characters and we're left with the last word in the cell:

Smith
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
That's a very good explanation but I'm left with just one important question.

What kind of ice cream???

:LOL:
 

Forum statistics

Threads
1,147,731
Messages
5,742,849
Members
423,758
Latest member
accountfornada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top