How to make Excel automatically assign values...

[kurtgarb798]

Hi,

I'm a researcher at Durham University in the United Kingdom and I have a spreadsheet that consists of news feeds and instead of manually going through the 40,000+ observations that are being updated daily and assigning valuse to each, is there a way to make Excel do it for me?

Basically, in cell A1 there is -

Galverston seawell being repaired after Hurricane Ike - USA Today
Moderate earthquake strikes Batanes - ABS CBN News
Drivers rescued from flood water - BBC

where " - USA Today" is the news agency.

Is there a way to make Excel find " - USA Today," " - ABS CBN News," " - BBC" and automatically input "USA Today" etc in cell B1, then "USA" in cell C1 and "2" in cell D1 for example, and so forth for the different news agencies.

Any help with this is much appreciated. We've been working on this database for weeks now and no one in my Department knows what to do.

Thank you.

 

[VoG]

To get the news agency in column B, select column A, Data > Text to Columns, specify Delimited with - as the delimiter.

I'm not sure about the rest of the question. Could you use a lookup table and VLOOKUP to return an news agency index number - see http://www.contextures.com/xlFunctions02.html

 

[kurtgarb798]

Hi VoG,

thanks for the speedy reply.

I have tried Text to Columns and it works for most but several thousand cells are like this:

A wake - up call to avert a real tragedy - Malaysia Star

and so Text to Columns returns "- up call to avert a real tragedy - Malaysia Star"

A collegue suggested Lookup tables or VLOOKUP but didn't know how to use them and I'm afraid I'm none the wiser. I've looked at your link and I'm still confused.

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

Thanks for the help.