Searching for a country in a cell's text string and having the result returned

[IntermediateUser]

Using Excel 2013 in Win 8.1, I would like Excel to find out if a country is mentioned in a list of newspaper headlines (column A) - and return the result in column B.
I have a list of all the world's countries (238 of them) in another column (C), and I would like Excel to check from this list.

This is an extract of how the list looks like:

[TABLE="width: 312"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Headline
[/TD]
[TD]Mentioned country[/TD]
[TD]Country
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 2"]Global Forum for Partnerships on Media and Information Literacy to take place next year in Canada
[/TD]
[TD]Afghanistan
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="colspan: 2"]Promoting debate on freedom of expression with Algerian Judiciary Power[/TD]
[TD]Albania[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="colspan: 2"]18th SEAPAVAA Conference: Audiovisual Archives: Why They Matter[/TD]
[TD]Algeria[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="colspan: 2"]Director-General urges investigation into the murder of Albanian journalist[/TD]
[TD]American Samoa[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD]…[/TD]
[TD][/TD]
[TD]…[/TD]
[/TR]
</tbody>[/TABLE]


Is this possible? I would be very thankful if anyone would suggest a formula.

 

[Andrew Poulsom]

Welcome to MrExcel.

Try in B2 confirmed with Ctrl+Shift+Enter and copied down:

=IFERROR(INDEX(C$2:C$5,SMALL(IF(ISNUMBER(SEARCH(C$2:C$5,A2)),ROW(C$2:C$5)-ROW($C$2)+1),1)),"None")

 

[hiker95]

IntermediateUser,

Welcome to the MrExcel forum.

I see that Andrew Poulsom has already given you a formula.


The below macro will adjust for a varying number of rows in columns A and C.

Sample raw data:

Excel 2007
	A	B	C
1	Headline	Mentioned country	Country
2	Global Forum for Partnerships on Media and Information Literacy to take place next year in Canada		Afghanistan
3	Promoting debate on freedom of expression with Algerian Judiciary Power		Albania
4	18th SEAPAVAA Conference: Audiovisual Archives: Why They Matter		Algeria
5	Director-General urges investigation into the murder of Albanian journalist		American Samoa
6			Canada
7
Sheet1

After the macro using two arrays in memory:

Excel 2007
	A	B	C
1	Headline	Mentioned country	Country
2	Global Forum for Partnerships on Media and Information Literacy to take place next year in Canada	Canada	Afghanistan
3	Promoting debate on freedom of expression with Algerian Judiciary Power	Algeria	Albania
4	18th SEAPAVAA Conference: Audiovisual Archives: Why They Matter		Algeria
5	Director-General urges investigation into the murder of Albanian journalist	Albania	American Samoa
6			Canada
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractCountry()
' hiker95, 12/15/2013
' http://www.mrexcel.com/forum/excel-questions/745218-searching-country-cells-text-string-having-result-returned.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long
a = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
c = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
For i = 1 To UBound(c, 1)
  For ii = 1 To UBound(a, 1)
    If InStr(a(ii, 1), Trim(c(i, 1))) Then a(ii, 2) = c(i, 1)
  Next ii
Next i
Range("A2").Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractCountry macro.

 

[martindwilson]

slightly shorter
=IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$5,A2),$C$2:$C$5),"none")

 

[IntermediateUser]

Andrew Poulsom,

Thank you for the welcome and your reply. I appreciate it.

The formula is definitely onto something. I should have underlined that the list of countries is just a resource, meaning that a country is not linked to the adjacent rows in column A and B. In other words, I would like the formula to look through the country list to find the country (no matter where in the list) that corresponds to the one mentioned in the headline. Using your formula, I only get a hit on Afghanistan (which is first in the list). I am not able to adjust the formula, so any further help is most appreciated. Thank you.

 

[IntermediateUser]

hiker95,

Thank you for the welcome and your reply. I appreciate it.

The macro functioned perfectly. It was well explained too. Thanks a lot!

 

[hiker95]

IntermediateUser,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[IntermediateUser]

martindwilson,

Thank you for your suggestion - and a good one at that! It functioned perfectly! Thank you!

 

[Andrew Poulsom]

Andrew Poulsom,

Thank you for the welcome and your reply. I appreciate it.

The formula is definitely onto something. I should have underlined that the list of countries is just a resource, meaning that a country is not linked to the adjacent rows in column A and B. In other words, I would like the formula to look through the country list to find the country (no matter where in the list) that corresponds to the one mentioned in the headline. Using your formula, I only get a hit on Afghanistan (which is first in the list). I am not able to adjust the formula, so any further help is most appreciated. Thank you.

I expect that you forgot to confirm the formula with Ctrl+Shift+Enter.

 

[IntermediateUser]

I expect that you forgot to confirm the formula with Ctrl+Shift+Enter.

I didn't. I even tried without that confirmation, just to be sure.

I got the problem solved anyway. Thank you for your contribution.