Excel 2007 : Extract text from string

[AmericanJesus]

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!

[JBeaucaire]

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.

Please Login or Register  to view this content.

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.

[AmericanJesus]

JBeaucaire,

That is perfect! Thank you so much for your assistance, this saves me a couple days worth of work.

[sailepaty]

Try this,

For Venue in B1
=LOOKUP(9.99E+307,SEARCH(Sheet2!$A$1:$A$579,A1),Sheet2!$A$1:$A$579)

For Ranking in C1
=LEFT(A1,FIND(" ",A1)-1)*1

For Artist's name in D1
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,""))

You will have to fix the #N/A manually.

Regards