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
RAW DATA | RESULT |
Mr George Banks | George Banks |
Mrs A Games | A Games |
Daniel Butler | Daniel Butler |
Miss B Timms | B Timms |
Prof Tom Hanks | Tom Hanks |
Dr K Brakes | K Brakes |
L Blaine | L Blaine |
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