middle function

[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?

 

[Peo Sjoblom]

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)

 

[Chip Pearson]

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

 

[Jay]

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?

 

[Frank Kabel]

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

 

[Frank Kabel]

Hi
try my suggestion. Should work in most cases

 

[hgrove]

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?

...

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

 

[Harlan Grove]

try my suggestion. Should work in most cases

It's fragile. It only works for strings s such that s = TRIM(s).