Index match text query

**alirulez** · 09-05-2013, 12:28 PM

Hi

I have an excel query and I'd appreciate your help.

I am trying to extract country names from a string text, and whilst most of my results are correct, there are some anomalies

If the string has the text 'woman'.. my formula matches 'oman'
If the string has the text 'indian' .. my formula matches 'india'
etc.

I need an exact match and I can't figure out how to do this.

I attach a sample file.Country formula.xls

**JBeaucaire** · 09-05-2013, 01:38 PM

Put a "space" at the start of each country name in the List of Countries. As long as the FIRST word in the string isn't the country, you'd be good.

**martindwilson** · 09-05-2013, 01:41 PM

you could include it in the formula
=INDEX('List of countries'!$B$3:$B$248,MATCH(TRUE,ISNUMBER(SEARCH(" "&'List of countries'!$B$3:$B$248," "&B2)),0))

**alirulez** · 09-05-2013, 02:38 PM

Thanks both, but I still have a problem where 'Indians' is being matched to 'India' and same for Japanese

I could put a space after the country name in the list of countries, but some of the text has full stops and commas after the country name, and again these won't match.

Any more ideas please let me know.

Many thanks in advance

**Ron Coderre** · 09-05-2013, 02:40 PM

Using your posted workbook...try this
• Add this item to the top of your country list
B2: No Match

• This regular formula returns the name of the stand-alone, embedded country...or No Match, if there are none

Formula:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

**Marcol** · 09-05-2013, 03:53 PM

Possibly this

To keep the formula simple use a dynamic named range

Name:= "Countries"
Refers to:=

Formula:

Please Login or Register  to view this content.

Then this array formula

Formula:

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter Before dragging Down.

Notes
1/. Yor profile is 2007 but the file provided is 2003, this is for 2007

2/. Extend the nested SUBSTITUTE() formula if there are more punctuation marks to allow for.

3/. If there is more than one country in the string the first found in the "Countries" range will be returned