Extract and Repost Names

[ManUBlueJay]

I AM working on a large geneoly project. I need to extract the names from a string and reposittion them if married Example: Jill [Jack Jackson] needs to be redone to say Jackson (Jack and Jill). The names always appear in that layout.

 

[Scott T]

Do you need the formula to check if they are married?

If not try

=TRIM(MID(RIGHT(A1,LEN(A1)-SEARCH("[",A1)),SEARCH(" ",RIGHT(A1,LEN(A1)-SEARCH("[",A1))),LEN(RIGHT(A1,LEN(A1)-SEARCH("[",A1)))-LEN(LEFT(RIGHT(A1,LEN(A1)-SEARCH("[",A1)),SEARCH(" ",RIGHT(A1,LEN(A1)-SEARCH("[",A1)))))))&" ["&LEFT(A1,SEARCH(" ",A1))&" and "&LEFT(MID(A1,SEARCH("[",A1)+1,999),SEARCH(" ",MID(A1,SEARCH("[",A1)+1,999))-1)&"]"

 

[ManUBlueJay]

No it doesn't. This works like a dream. Thanks a lot. I built half way but could not complete. Much appreciated.

 

[admiral100]

And another way

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1))),"]"," (")&SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,LEN(A1))),"[","")&" and "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(1-1)*LEN(A1)+1,LEN(A1)))&")"

 

[Rick Rothstein]

This works like a dream.

I thought you wanted the square brackets replaced with normal parentheses?

 

[ManUBlueJay]

I did, but I was ably to tweak that.

 

[ManUBlueJay]

This is using 84 less characters. Nice job. Thanks

 

[Rick Rothstein]

This is using 84 less characters. Nice job. Thanks

This formula is a little bit shorter still (and, like admiral100's formula, it also does the bracket/parentheses swap as well)...

=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1,FIND("[",A1))+1,99),"]"," ")&REPLACE(REPLACE(A1,FIND(" ",A1,FIND("[",A1))+1,0,"and "&LEFT(A1,FIND("[",A1)-2)&")"),1,FIND("[",A1),"("),MID(A1,FIND(" ",A1,FIND("[",A1))+1,99),"")