Find and Identify Surnames

[Minstrel]

I need to be able to find specific surnames from within long text strings (text includes numbers and characters formatted as text).
There is no common indicator, the surname may or may not have spaces or commas preceding or following it.
Once found, I need to identify which surname it is by placing a two digit code in column A of the same row (the text is in column G)
Currently I have 9 surnames which need to be found and identified, although this will increase over time
Anyone have any ideas?

Thanks

Thomas

 

[Boller]

Assuming :

- the list of names is on "Sheet2" column A starting in A2
- the 2 digit code is on "Sheet2" in column B starting in B2
- the column G data is on "Sheet1" starting in G2

Dim nSh As Worksheet, dSh As Worksheet
Dim nList As Range, dRng As Range, cell As Range, sRng As Range
Set nSh = Sheets("Sheet2")
Set dSh = Sheets("Sheet1")
Set nList = nSh.Range(nSh.[A2], nSh.[A65536].End(xlUp))
Set dRng = dSh.Range(dSh.[G2], dSh.[G65536].End(xlUp))
dSh.[A2:A65536].ClearContents
For Each cell In nList
    Set sRng = dRng.Offset(, -6).SpecialCells(xlCellTypeBlanks)
    With sRng
        .FormulaR1C1 = "=SEARCH(""" & cell & """,RC[6])"
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, 1) = cell(1, 2)
        .SpecialCells(xlCellTypeFormulas, 16).ClearContents
        On Error GoTo 0
    End With
Next