How do I extract the last name in Excel, from a list of names lik.

[Guest]

Trying to extract last name from a cell (db field) which contains names like:

John Doe
John B. Doe
J.B. Doe

Various combinations of formulas such as =Right(C17,Len(c17)-FIND(" ", C17))
Can't find the right combination to do the job.

Thanks

 

[Guest]

Hi
try:
=MID(A1,LOOKUP(2,1/(MID(A1,seq,1)=" "),seq)+1,1024)

where seq is a defined name ('Insert- Name - Define') with the formula
seq: =ROW(INDIRECT("1:1024"))

 

[Guest]

Frank:

Thank you for posting a solution. The solution did not work. I must be
misunderstanding the use of seq and 1024.

Thank you.
Robert

 

[R.VENKATARAMAN]

you can do text to column with <space> as delimiter but the last name may
not be in the same column

 

[Guest]

Hi
then try:
=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="
"),ROW(INDIRECT("1:1024")))+1,1024)