Add salutation to a surname

Issie_52

New Member
Joined
Jul 22, 2013
Messages
25
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.
 

Some videos you may like

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
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top