Hi,

I'm a postgraduate student at Durham University in the United Kingdom and my research is on media coverage and humanitarian assistance of Natural Hazards and is heavily Excel-based but myself and my supervisors seem to have come to a brick wall and no one in my whole department know what to do.

We are looking for a function or formula that can return a number from an alphanumeric string that relates to a keyword in that string.

I shall explain...

We have one column that looks like:

7 Vietnamese women die in quarry collapse--officials - Inquirer.net
1 dead, 1 missing as avalanches hit stormy California - San Diego Union Tribune
1 person dead, 1 missing skier found in SoCal avalanche - San Jose Mercury News
Landslide kills 7 on Indonesia's Java - Reuters UK

And we need a column that shows only the number which relates to fatalities mentioned in each title.

I have managed to find a way of extracting the numbers from the alphanumeric strings but we have problems with titles like this:

Mingo National Wildlife Refuge Going Through "100 Year Flood" - KFVS

So we are looking for a way to extract numbers from the strings that contain keywords like "dead" "deaths" "fatalities" etc.

We are also looking for a way to extract the last part of the above strings:

- KFVS
- Reuters UK
- San Jose Mercury News

and then automatically assign abbreviated codes and country names to the news agency specified.

The final database should look something like this:

"Landslide kills 7 on Indonesia's Java - Reuters UK" - Reuters UK - REU - UK - 7

No one in my entire university can figure this out.

Can you please help????