middle function

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I am trying to extract the middle initial from a group of
names. How do I set the number of characters criteria to
search for the second space in the name?
 
You can still use the first space assuming the middle initial is one
character

=MID(A1,FIND(" ",A1)+1,1)



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Jay,

If the name is in normal format of "first mi last", use a formula
like

=MID(A1,FIND(" ",A1)+1,1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Sorry, I should have specified better. Some are middle
initials with a period, some are full middle names. I
need to extract the full middle name or the middle initial
with the period. How do I go about that?
 
Hi
one way to get the middle initial
=IF(ISERROR(FIND("^^",SUBSTITUTE(A1," ","^^",2))),"",TRIM(MID(A1,FIND("
",A1)+1,FIND("^^",SUBSTITUTE(A1," ","^^",2))-FIND(" ",A1)-1)))
 
Jay wrote...
Sorry, I should have specified better. Some are middle
initials with a period, some are full middle names. I
need to extract the full middle name or the middle initial
with the period. How do I go about that?
Click to expand...
...

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))>1,
MID(TRIM(A1),FIND(" ",TRIM(A1))+1,FIND(" ",TRIM(A1),
FIND(" ",TRIM(A1))+1)-FIND(" ",TRIM(A1))-1),""
 
Back
Top