how to get the middle name

Z

zomex

how can i get the middle name of "fazain iqbal khan"... i can get the last
name by =(a2,len(a2)-search("#", substitute(a2&" "," ","#",2)))... and first
name=right(a2,find(" ",a2))
 
H

Harlan Grove

Roger Govier wrote...
Try
=MID(A2,FIND(" ",A2)+1,FIND("#",SUBSTITUTE(A2,
" ","#",2))-FIND("#",SUBSTITUTE(A2," ","#",1))-1)
....

Satisfies the OP's specs, but how would it handle George Herbert Walker
Bush? Or Malcolm X? For names like those,

=MID(A2,FIND(" ",A2)+1,FIND("#",SUBSTITUTE(A2,
" ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))
 
R

Roger Govier

Hi

Try
=MID(A2,FIND(" ",A2)+1,FIND("#",SUBSTITUTE(A2,"
","#",2))-FIND("#",SUBSTITUTE(A2," ","#",1))-1)

Regards

Roger Govier
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top