removing multiple single characters from cells

[tkjacob]

I have been searching removing middle initials on the forums and have found multiple helpful, useful and functioning results except for when in this combination. I am trying to remove middle initials when there are 2 names in a cell. For example:

John J & Mary M Johnson
Marc L & Jenny K Jackson

I would like to easily convert these to just read:
John & Mary Johnson
Marc & Jenny Jackson

When I use the two most useful options the find/replace ' * ' AND the formula
=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
the results turn into:
John Johnson
Marc Jackson

Anyone have a different solution? I tried changing the & to 'and' which still generated the same results.

 

[azumi]

Perhaps:

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

 

[azumi]

Sorry missed something:

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

 

[XOR LX]

Hi,

Perhaps:

=TRIM(REPLACE(REPLACE(SUBSTITUTE(A1," ",REPT(" ",999)),999,999,""),3*999,999,""))

Regards

 

[Rick Rothstein]

Here is a general formula that will work for single names (like Cher), normal names (like John Jackson), normal names with a middle initial/name (John Philip Sousa), and two first names coupled by an ampersand with or without middle initials/names (like John & Mary Jackson, John Jones & Mary Jackson, John & Mary Sue Jackson and John Jones & Mary Sue Jackson)...

=LEFT(A1,FIND(" ",A1&" "))&IF(COUNTIF(A1,"*&*"),"& "&TRIM(MID(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"& ",REPT(" ",99)),99))," ",REPT(" ",999)),1,999))&" ","")&IF(COUNTIF(A1,"* *"),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"")

 

[tkjacob]

Brilliant perfection! Thank you!

here is a general formula that will work for single names (like cher), normal names (like john jackson), normal names with a middle initial/name (john philip sousa), and two first names coupled by an ampersand with or without middle initials/names (like john & mary jackson, john jones & mary jackson, john & mary sue jackson and john jones & mary sue jackson)...

=left(a1,find(" ",a1&" "))&if(countif(a1,"*&*"),"& "&trim(mid(substitute(trim(right(substitute(a1,"& ",rept(" ",99)),99))," ",rept(" ",999)),1,999))&" ","")&if(countif(a1,"* *"),trim(right(substitute(a1," ",rept(" ",99)),99)),"")

 

[Armando Montes]

Try Find & Replace:
Find what: space?space
Replace with: single space

 

[Rick Rothstein]

Try Find & Replace:
Find what: space?space
Replace with: single space

Not everyone has a middle name (for example, my wife), so, given that, try your solution on these...

John & Mary Jones

Jack & Sally K Jones

 

[István Hirsch]

This formula also seems to work with names mentioned in #1 and #5 except Cher:

=TRIM(LEFT(A1,FIND(" ",A1))&IFERROR("&"&MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("&",SUBSTITUTE(A1," ",REPT(" ",99)))+1,198),"")&RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))