Add salutation to a surname

[Issie_52]

Good day,

I am trying to add Mr & Mrs to a formula. I have a long list of names, both single (Mr J Smith) and married couples (Mr AB & Mrs CDE Smith)

Currently the formula looks like this:
=IFERROR("Dear "&LEFT(A5,FIND(" ",A5,1)-1)&" "&RIGHT(A5,LEN(A5)-FIND("*",SUBSTITUTE(A5," ","*",LEN(A5)-LEN(SUBSTITUTE(A5," ",""))))),"-")
resulting in: Dear Mr Smith

I need it to read Dear Mr & Mrs Smith.

Any feedback/help will be appreciated.

 

[AlanY]

try

=IF(ISNUMBER(SEARCH("Mrs",A5)),"Dear Mr & Mrs "&TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",500)),500)),"Dear Mr "&TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",500)),500)))

 

[tyija1995]

Hi Issie,

Try this formula:
=IFERROR("Dear "&LEFT(A5,FIND(" ",A5,1)-1)&" "&IFERROR(IF(SEARCH("Mrs",A5)>0,CHAR(38)&" Mrs ",""),"")&RIGHT(A5,LEN(A5)-FIND("*",SUBSTITUTE(A5," ","*",LEN(A5)-LEN(SUBSTITUTE(A5," ",""))))),"-")

N.B. If you have values such as "Miss" "Ms" etc it won't pick it up currently, you'll need to adjust the formula

 

[AlanY]

try

=IF(ISNUMBER(SEARCH("Mrs",A5)),"Dear Mr & Mrs "&TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",500)),500)),"Dear Mr "&TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",500)),500)))

more compact version

="Dear Mr "&IF(ISNUMBER(SEARCH("Mrs",A5)),"Mrs ","")&TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",500)),500))

 

[steve the fish]

You may need to adjust for a single lady eg Mrs J Smith

<colgroup><col width="152"></colgroup><tbody>
</tbody>

 

[AlanY]

You may need to adjust for a single lady eg Mrs J Smith

<colgroup><col width="152"></colgroup><tbody>
</tbody>

we need to know how many others to work on

Mr
Mrs
Miss
Ms
Dr
Lord
Lady

etc
etc

 

[steve the fish]

This seems to work regardless of the salutation provided the ampersand is used to join the two people:

=TRIM(LEFT(SUBSTITUTE(A5," ",REPT(" ",99)),99)&IFERROR(" "&MID(SUBSTITUTE(A5," ",REPT(" ",99)),FIND("&",SUBSTITUTE(A5," ",REPT(" ",99))),99*2),"")&RIGHT(SUBSTITUTE(A5," ",REPT(" ",99)),99))

 

[Issie_52]

Thank you so much! Worked like a charm

 

[Issie_52]

Thank you, Alan. This also works!