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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
try:

=IF(SUM(SUMPRODUCT(--(LEFT(A1,SEARCH(" ",A1)-1)={"Mr","Mrs","Miss","Prof","Dr"})))>0,MID(A1,SEARCH(" ",A1)+1,256),A1)
 
Upvote 0
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"}
 
Upvote 0
Thank you ever so much.

It works brilliantly.
Setting up an array will help me future proof it as well.
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top