extract last word of a sentence regardless the number of spaces

[danielhvf]

I've a list of names with different numbers of spaces, i would like a formula to give me the last name regardless the number of spaces...

is that possible?

List example:

Amélia Alexandra Correia Almeida

Maria Albertina Alves Moreira Figueiredo

Mário Miguel de Oliveira Azevedo Feitor

Carla Maria Bastos Soares Carlos Sousa Teixeira

<colgroup><col></colgroup><tbody>
</tbody>

extract Almeida, Figueiredo, Feitor, Soares and Teixira

 

[Momentman]

Excel 2010

	A	B
1	Amélia Alexandra Correia Almeida	Almeida
2	Maria Albertina Alves Moreira Figueiredo	Figueiredo
3	Mário Miguel de Oliveira Azevedo Feitor	Feitor
4	Carla Maria Bastos Soares	Soares
5	Carlos Sousa Teixeira	Teixeira

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Drag the formula down to as many rows as you require

Worksheet Formulas Cell Formula B1 =RIGHT(A1,LEN(A1)-FIND("**",SUBSTITUTE(A1," ","**",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[barry houdini]

Here's another way

=REPLACE(A1,1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")

 

[István Hirsch]

..Or give this a try:

=TRIM(RIGHT(SUBSTITUTE(A1," ";REPT(" ",99)),99))

 

[danielhvf]

tnks!!!

houdini's formula worked for me (excel 2007)

 

[István Hirsch]

Sorry, my formula correctly:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))