Extracting numbers related to certain keywords from alphanumeric strings

[kurtgarb798]

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.

Pleas Help!

 

[shyrath]

What I would do (as long as you already know how to extract numbers from text) is the following.

First test the string if it contains one of your keywords ( if there are to many, i would put them into an array).

I quickly tested the below, by "uppercasing (UCASE)" the string first you make it case insensitive if you write your keywords in uppercase.

Sub testing1()

For Each c In Range("E1:E50")

If UCase(c.Value) Like "*DIE*" Or UCase(c.Value) Like "*FATAL*" Then

MsgBox c.Address & " contains a key-value"
'your code to extract the numbers go here

Else
End If

Next c


End Sub

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

 

[kurtgarb798]

Hi shyrath,

Thank you so much for the speedy reply.

Where exactly do I put the code you posted?

Also, the way I extracted the numbers from the text string was using a UDF from this site:

http://www.ozgrid.com/VBA/ExtractNum.htm

and used "=ExtractNumber(F3,TRUE)" which seemed to work.

 

[shyrath]

If you like, send me the file (use the link just below here) and i´ll try to help you along
Assuming you use formulas and no macro´s (yet?) the code I posted is a macro.