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

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

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

"RobertC" wrote:

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

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

"Frank Kabel" wrote:

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

"RobertC" wrote:

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

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


RobertC wrote in message
...
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

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



"rccline" wrote:

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

"Frank Kabel" wrote:

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

"RobertC" wrote:

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