extract last word of a sentence regardless the number of spaces

danielhvf

New Member
Joined
Nov 22, 2013
Messages
4
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Excel 2010
AB
1Amélia Alexandra Correia AlmeidaAlmeida
2Maria Albertina Alves Moreira FigueiredoFigueiredo
3Mário Miguel de Oliveira Azevedo FeitorFeitor
4Carla Maria Bastos SoaresSoares
5Carlos Sousa TeixeiraTeixeira

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
Drag the formula down to as many rows as you require
Worksheet Formulas
CellFormula
B1=RIGHT(A1,LEN(A1)-FIND("**",SUBSTITUTE(A1," ","**",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,215,467
Messages
6,124,984
Members
449,201
Latest member
Lunzwe73

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