Find and Identify Surnames

Minstrel

Board Regular
Joined
Jan 2, 2007
Messages
69
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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