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:
[TABLE="width: 270"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Amélia Alexandra Correia Almeida[/TD]
[/TR]
[TR]
[TD]Maria Albertina Alves Moreira Figueiredo[/TD]
[/TR]
[TR]
[TD]Mário Miguel de Oliveira Azevedo Feitor[/TD]
[/TR]
[TR]
[TD]Carla Maria Bastos Soares
Carlos Sousa Teixeira[/TD]
[/TR]
</tbody>[/TABLE]

extract Almeida, Figueiredo, Feitor, Soares and Teixira

 

[Momentman]

Excel 2010

	A	B
Almeida
Figueiredo
Feitor
Soares
Teixeira

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Amélia Alexandra Correia Almeida[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Maria Albertina Alves Moreira Figueiredo[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Mário Miguel de Oliveira Azevedo Feitor[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Carla Maria Bastos Soares[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Carlos Sousa Teixeira[/TD]

</tbody>

Sheet2​

Drag the formula down to as many rows as you require
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=RIGHT(A1,LEN(A1)-FIND("**",SUBSTITUTE(A1," ","**",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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