Formula to return the last word in a cell

bkaehny

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

njimack

Well-known Member
You're welcome, thanks for the feedback

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

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!

njimack

Well-known Member
This formula entered in B2 and copied down:

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

Very impressive!

T. Valko

Well-known Member

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
That's a very good explanation but I'm left with just one important question.

What kind of ice cream???

*** ignore ***

Replies
3
Views
176
Replies
2
Views
411
Replies
1
Views
107
Replies
1
Views
2K
Replies
6
Views
600

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

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.

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

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