Formula to return the last word in a cell

[bkaehny]

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]

You're welcome, thanks for the feedback

 

[circledchicken]

Hi,

Another possible solution based on njimack's example might be:

Excel Workbook
	A	B	C
1	John Smith	Smith	Smith
2	John Q Public	Public	Public
3	Herald van der Schnossen	Schnossen	Schnossen
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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]

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:

Book1
	A	B
2	John Smith	Smith
3	Cher	Cher
4	John Q Public	Public
5	Herald van der Schnossen	Schnossen
Sheet1

This formula entered in B2 and copied down:

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

 

[bkaehny]

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

Book1
*	A	B
2	John Smith	Smith
3	Cher	Cher
4	John Q Public	Public
5	Herald van der Schnossen	Schnossen
Sheet1

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.

 

[T. Valko]

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]

This formula entered in B2 and copied down:

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

Very impressive!

 

[T. Valko]

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]

That's a very good explanation but I'm left with just one important question.

What kind of ice cream???

 

[T. Valko]

What kind of ice cream???

Turkey Hill - Orange Cream Swirl

 

[Rick Rothstein]

*** ignore ***