Remove middle name for end of a name string

**maacmaac** · 07-06-2011, 02:04 PM

I have a column that is formatted as:
FirstName, LastName MiddleName/MiddleInitial

I am trying remove the middle name or middle initial from the string. Basically remove everything after the second comma or after second space in string. Thanks in advance for any assistance.

Example cells as follows:
Blassingham, James H., III
Bleke, Michael L
Bradford, William, Jr
Brogin, Ira
Bunsa, Robert J., Jr

Desired output is as follows:
Example cells as follows:
Blassingham, James
Bleke, Michael
Bradford, William
Brogin, Ira
Bunsa, Robert

**NBVC** · 07-06-2011, 02:15 PM

Try:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,A1,SUBSTITUTE(LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",2))-1),",","",2))

where A1 has first original name...

**maacmaac** · 07-06-2011, 03:59 PM

Perfect. Thanks for solution.

**albertan** · 03-11-2018, 04:19 PM

Sorry, but what does ^^ mean in a formula? What is it looking for? Thanks!

**Learning!** · 07-20-2021, 04:35 PM

Just saw this thread and it helped me as well; Thank you NBVC!

**Rick Rothstein** · 07-20-2021, 06:21 PM

Here is a shorter formula that will also work...

Formula:

Please Login or Register  to view this content.

and here is an even shorter formula that will also work as well...

Formula:

Please Login or Register  to view this content.

**Learning!** · 07-21-2021, 09:50 AM

Thank you, Rick!

**Mateo_Superstore** · 08-30-2022, 04:30 PM

Hi, I'm working in Office 365 and tried copying all 3 formulas listed above and nothing is working. I'm still learning, can someone help me with a formula that will do the above in 365?

I've tried using LEN and LEFT but I'm still learning formulas. (any explanation would be most appreciated as well).

Thank you!

**AliGW** · 08-30-2022, 04:44 PM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread