# Add salutation to a surname

#### Issie_52

##### New Member
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### AlanY

##### Well-known Member
try

Code:
``=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

##### Well-known Member
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

##### Well-known Member
try

Code:
``=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

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

#### steve the fish

##### Well-known Member

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

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

#### AlanY

##### Well-known Member
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

etc
etc

#### steve the fish

##### Well-known Member

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

##### New Member
Thank you so much! Worked like a charm

#### Issie_52

##### New Member
Thank you, Alan. This also works!

Replies
3
Views
78
Replies
8
Views
385
Replies
1
Views
54
Replies
1
Views
305
Replies
3
Views
323