To remove salutation from name in Excel but not all names have salutation

Bindi

New Member
Joined
Dec 8, 2016
Messages
3
Hi,

I have a long list of names and I need to remove saluation from names in a column but not all the names have saluation.
Eg some are Mr John Jones and some are John Jones.

Please help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Bindi,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
With the names in column A and a list of salutations in column C

Code:
Sub kev1()
lastrowA = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lastrowC = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To lastrowA
For j = 1 To lastrowC
k = Len(Cells(j, 3))
Debug.Print Cells(i, 1), Cells(j, 3), k
If Left(Cells(i, 1), k + 1) = Cells(j, 3) & " " Then
    Cells(i, 2) = Right(Cells(i, 1), Len(Cells(i, 1)) - k - 1)
End If
Next j
Next i
End Sub

The results you want are in column B
 

Bindi

New Member
Joined
Dec 8, 2016
Messages
3
Thanks for your help but unfortunately, the macros does not work. The macros only takes me to the end of the column.

Following is the screen print of what I am trying to achieve. Some names do not have saluation in the cell.
RAW DATARESULT
Mr George BanksGeorge Banks
Mrs A Games A Games
Daniel ButlerDaniel Butler
Miss B TimmsB Timms
Prof Tom HanksTom Hanks
Dr K BrakesK Brakes
L BlaineL Blaine

<tbody>
</tbody><colgroup><col><col></colgroup>
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

try:

=IF(SUM(SUMPRODUCT(--(LEFT(A1,SEARCH(" ",A1)-1)={"Mr","Mrs","Miss","Prof","Dr"})))>0,MID(A1,SEARCH(" ",A1)+1,256),A1)
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
with the below part you could create a named range and put any possible instances in there as opposed to manually changing the formula.

Also this is an array, you need to enter it with ctrl+shift+enter

{"Mr","Mrs","Miss","Prof","Dr"}
 

Bindi

New Member
Joined
Dec 8, 2016
Messages
3

ADVERTISEMENT

Thank you ever so much.

It works brilliantly.
Setting up an array will help me future proof it as well.
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
You're very welcome
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Bindi, if you were looking for a VBA solution and your data is consistent with what you have posted then personally I think that a straight Replace would be the easiest option...

Code:
Sub ReplaceSalute()
    Dim MyArr, i As Long
    MyArr = Array("MR ", "Mrs ", "Miss ", "Prof ", "Dr ")
    For i = LBound(MyArr) To UBound(MyArr)
        Columns("A").Replace What:=MyArr(i), Replacement:="", LookAt:=xlPart
    Next
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,789
Members
430,569
Latest member
sbardelli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top