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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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,350
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,421

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,421
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

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