VBA remove string custom salutation name from list table excel

I have string name with salutation

DATIN SERI PADUKA JOHN DOE with actual name is JOHN DOE

and i have list excel for salutation :

DATIN SERI
DATIN SERI PADUKA

Currently using substitute function from VBA :

For index=2 to totalRow

SalutationArray = Split(ws.Range("A"&index), " ")
For N = 0 to UBound(SalutationArray)
strSalutation = SalutationArray(N)
ws.Range("B" & index) = WorksheetFunction.Substitute(strName,strSalutation,"")

Next index

The output result is PADUKA JOHN DOE with space in front

how to achieve output result is only JOHN DOE with that list salutation

Solution

With this code create a dictionary of the salutations by word. After it the code removes all words found. Apply as a function and the return value will be the pure name.

Sub sallute()  'function sallute(inp as string) as string  REPLACE
purename = "DATIN SERI PADUKA JOHN DOE"  'purename=inp  REPLACE
Set salrng = Range("A1:A2")  'the range of salutations
Dim dict As Scripting.Dictionary
Set dict = New Dictionary
For Each cel In salrng

spl = Split(cel, " ", , vbTextCompare)
For i = 0 To UBound(spl)
If Not dict.Exists(spl(i)) Then
dict.Add spl(i), spl(i)
End If
Next i
Next cel

On Error Resume Next  'eliminate missing salutation
For i = 0 To dict.Count - 1

strSalutation = dict.Keys(i)
purename = WorksheetFunction.Substitute(purename, strSalutation, "")
Next i
On Error GoTo 0
purename = Trim(purename)
'sallute=purename   REPLACE
End Sub

UPDATE

To enable the usage of partially identical salutations can several solutions applied, for me seems to enhance the check with the leading/trailing spaces and replace the found word with a space. For this apply the following mod: (in 3 lines)

Sub sallute()  'function sallute(inp as string) as string  REPLACE
purename = " " & "DATIN SERI PADUKA JOHN DOE" & " " 'purename=inp  REPLACE MOD
Set salrng = Range("A1:A2")  'the range of salutations
Dim dict As Scripting.Dictionary
Set dict = New Dictionary
For Each cel In salrng

spl = Split(cel, " ", , vbTextCompare)
For i = 0 To UBound(spl)
If Not dict.Exists(spl(i)) Then
dict.Add spl(i), spl(i)
End If
Next i
Next cel

On Error Resume Next  'eliminate missing salutation
For i = 0 To dict.Count - 1

strSalutation = " " & dict.Keys(i) & " "    'MOD
purename = WorksheetFunction.Substitute(purename, strSalutation, " ")   'MOD
Next i
On Error GoTo 0
purename = Trim(purename)
'sallute=purename   REPLACE
End Sub