Finding and assigning data to keywords...

[kurtgarb798]

Hi,

I'm looking for a way to search for certain words in a column and then return specific codes for each word in a different column.

I basically need something like "if A1 contains USA Today then input USAT in B1, if cell A1 contains BBC then return BBC in cell B1, ."

I have a list of 41,000 news headlines in column A like this -

Galveston seawall being repaired after Hurricane Ike - USA Today
Gaza 'looks like earthquake zone' - BBC News
Magnitude-3.1 Earthquake Shakes Reseda Area - MSNBC

and I want to search each one and automatically assign individual abbreviated news agency codes and country names to each news agency listed.

The final database should be split across three columns like this:

"Galveston seawall being repaired after Hurricane Ike - USA Today" - USAT - USA
"Gaza 'looks like earthquake zone' - BBC News" - BBC - UK
"Magnitude-3.1 Earthquake Shakes Reseda Area - MSNBC" - MSNBC - USA

No one in my entire university can figure out how to do this.

Pleas Help!

 

[Georgiboy]

You could use a custom function a bit like this...

Function InString(rCell As Range)

If InStr(UCase(rCell), "BLUE") Then InString = "Contains Blue"
If InStr(UCase(rCell), "PINK") Then InString = "Contains Pink"
If InStr(UCase(rCell), "YELLOW") Then InString = "Contains Yellow"

End Function

This looks for the three colour words in a string and then returns "Contains Blue" or whatever colour of the three.

 

[kurtgarb798]

Thank you so much for your help. That worked really well and I've managed to use it for a few other things too.

Thanks.