Excel 2007 : Extract text from string

Good Afternoon,

I'm working on a project involving large amounts of data. I've attached a sample of the raw data.

Here is a brief description of what I am trying to accomplish.

In sheet 1, Column A I have raw data, formatted as follows:
Rank Artist,[artist 2] Venue.

In sheet 2 Column A, I have a list of venues.

This data set spans over 6000 such entries. I am looking for a way to extract the Venue name from Sheet 1 Column A and place it into Column B, by matching it up with the venues listed in Sheet 2.

Additionally, I would also like to extract the rank and the artist. Because the data is not uniform all around, I can't easily parse the data. If an error occurs, I just want to leave it blank so that I can edit it manually.

Any help you could provide would be greatly appreciated!

Thank you!

I couldn't think of any simple way to get your column B values, but I tweaked a macro I had to do it for you.

Option Explicit

Sub GetVenues()
'Jerry Beaucaire   5/13/2010
'Extract venue names from long strings using a list of venues
Dim LastRw  As Long
Dim CNT     As Long
Dim Venues  As Range
Dim Venue   As Range
Application.ScreenUpdating = False

Set Venues = Sheets("Sheet2").Range("A:A").SpecialCells(xlCellTypeConstants)

With Sheets("Raw Data")
    .Rows(1).Insert xlShiftDown
    .Range("A1") = "key"
    CNT = 1
    For Each Venue In Venues
        Application.StatusBar = CNT & " of " & Venues.Cells.Count
        .Range("A:A").AutoFilter Field:=1, Criteria1:="*" & Venue & "*"
        LastRw = .Range("A" & .Rows.Count).End(xlUp).Row
        If LastRw > 1 Then _
            .Range("B2:B" & LastRw).Value = Venue
        CNT = CNT + 1
    Next Venue
    
    .AutoFilterMode = False
    .Rows(1).Delete xlShiftUp
    .Range("A:B").Columns.AutoFit
End With

Set Venues = Nothing
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select GetVenues from the macro list.