Thread: how to get the middle name
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default how to get the middle name
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))

Reply With QuoteReply With Quote