Need Formula for Extraction of Middle Initial or Name

[bri49er80]

Hello,

I am in need of help for an excel formula that will extract either
middle initial or middle name as follows:

Change:
John T. Doe
or
John Tom Doe

To:
John Doe

Can anyone help me out? Thanks in advance.
Brian

(e-mail address removed)

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/FirstLast.htm

 

[Jason Morin]

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

Be aware this will only work when 3 words are present. It
will fail with names like "Jo Ann T. Doe" or "Norman Clyde
von Helsing".

HTH
Jason
Atlanta, GA

 

[bri49er80]

Thanks for the reply. The problem is as you said it it will only wor
on 3 names. I have a large spreadsheet that has names in the followin
format, which is why I also cannot use the text to column feature.

John Doe
John T. Doe
John Tom Doe

Is there a formula that will make each record appear as "John Doe?"

Thanks again
Brian
(e-mail address removed)

 

[Jason Morin]

If some names lack a middle name or middle initial, you
can extend the formula to:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,A1,SUBSTITUTE
(A1,MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND
(" ",A1)),""))

HTH
Jason
Atlanta, GA

 

[bri49er80]

Excel indicates the formula has an error and will not work. Am I
missing something?
Brian

 

[RagDyer]

Try this.
It will extract the first word and add it, with a space, to the last word.

So, Baron Ludwig von Helsing will end up
Baron Helsing.

=LEFT(A1,FIND(" ",A1)-1)&" "&RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Excel indicates the formula has an error and will not work. Am I
missing something?
Brian