Counting number of text strings in a cell

[lyman]

Hello

I would like to write a formula that would give me the number of EU countries listed in a single cell. So for instance if I was to check the below cell the result of the formula would be 3.

CELL A1: Australia,China,Germany,France,South Africa,Spain

There are 28 EU countries so I was thinking to nest the If function 28 times (each EU country spelt out) with ISNUMBER and SEARCH function. However I don't know how to then count the number of times there would be a match in the cell.

Much appreciate suggestions. Maybe there is an easy way of doing this and I'm thinking completely wrong about it.

Thanks

 

[lyman]

Sorry typo my typo, I should have written 28 above, as you found. However the formula in my spreadsheet is finding 5 only. I'm sure i adapted as necessary.
{=SUM(--ISNUMBER(SEARCH($F$1:$F$28,A1)))}

The list (F1:F28) I use is:

Austria

Belgium

Bulgaria

Croatia

Cyprus

Czech Republic

Denmark

Estonia

Finland

France

Germany

Greece

Hungary

Ireland

Italy

Latvia

Lithuania

Luxembourg

Malta

Netherlands

Poland

Portugal

Romania

Slovakia

Slovenia

Spain

Sweden

United Kingdom

<colgroup><col></colgroup><tbody>
</tbody>

 

[MARK858]

Your list is the same as mine and there are all 28 counties in your text string which is what I get with Fluff's formula.

Excel 2010
	A	B	C	D	E	F
1	Uganda,Zimbabwe,Zambia,Yemen,Vietnam,Venezuela,Vatican City (Holy See),Vanuatu,Uzbekistan,Uruguay,United States Of America,United States Minor Outlying Islands,United Kingdom,United Arab Emirates,Afghanistan,Albania,Algeria,Andorra,Angola,Antigua And Barbuda,Argentina,Armenia,Australia,Austria,Azerbaijan,Bahamas,Bahrain,Bangladesh,Barbados,Belarus,Belgium,Belize,Benin, Bermuda,Bhutan,Bolivia,Bosnia And Herzegovina,Botswana,Brazil,Brunei,Bulgaria,Burkina Faso,Burundi,Cambodia,Cameroon,Canada,Cape Verde,Central African Republic,Chad,Chile,China,Colombia,Comoros,Costa Rica,Croatia,Cuba,Cyprus,Czech Republic,Democratic Republic Of The Congo,Denmark,Djibouti,Dominica,Dominican Republic,Ecuador,Egypt,El Salvador,Equatorial Guinea,Eritrea,Estonia,Eswatini (Formerly Swaziland),Ethiopia,Fiji,Finland,France,Gabon,Gambia,Georgia,Germany,Ghana,Greece,Grenada,Guatemala,Guinea,Guinea-Bissau,Guyana,Haiti,Honduras,Hong Kong,Hungary,Iceland,India,Indonesia,Iran,Iraq,Ireland,Israel,Italy,Jamaica,Japan,Jordan,Kazakhstan,Kenya,Kiribati,Kosov o,Kuwait,Kyrgyzstan,Laos,Latvia,Lebanon,Lesotho,Liberia,Libya,Liechtenstein,Lithuania,Luxembourg,Macedonia (Fyrom),Madagascar,Malawi,Malaysia,Maldives,Mali,Malta,Marshall Islands,Mauritania,Mauritius,Mexico,Micronesia,Moldova,Monaco,Mongolia,Montenegro,Morocco,Mozambique,Myanmar (Formerly Burma),Namibia,Nauru,Nepal,Netherlands,New Zealand,Nicaragua,Niger,Nigeria,North Korea,Norway,Oman,Pakistan,Palau,Palestine,Panama,Papua New Guinea,Paraguay,Peru,Philippines,Poland,Portugal,Qatar,Republic Of The Congo,Romania,Russia,Rwanda,Saint Kitts And Nevis,Saint Lucia,Saint Vincent And The Grenadines,Samoa,San Marino,Sao Tome And Principe,Saudi Arabia,Senegal,Serbia,Seychelles,Sierra Leone,Singapore,Slovakia,Slovenia,Solomon Islands,Somalia,South Africa,South Korea,South Sudan,Spain,Sri Lanka,Sudan,Suriname,Swaziland,Sweden,Switzerland,Syria,Taiwan,Tajikistan,Tanzania,Thailand,Timor-Leste,Togo,Tonga,Trinidad And Tobago,Tunisia,Turkey,Turkmenistan,Tuvalu,Ukraine	28				Austria
2						Belgium
3						Bulgaria
4						Croatia
5						Cyprus
6						Czech Republic
7						Denmark
8						Estonia
9						Finland
10						France
11						Germany
12						Greece
13						Hungary
14						Ireland
15						Italy
16						Latvia
17						Lithuania
18						Luxembourg
19						Malta
20						Netherlands
21						Poland
22						Portugal
23						Romania
24						Slovakia
25						Slovenia
26						Spain
27						Sweden
28						United Kingdom
Sheet3

Cell Formulas
Range	Formula
B1	{=SUM(--ISNUMBER(SEARCH($F$1:$F$28,A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I'll upload a copy of the workbook if it helps for you to look at.

 

[lyman]

If you could upload your workbook would help a lot. Thanks

 

[MARK858]

If you could upload your workbook would help a lot. Thanks

Done

https://app.box.com/s/s6jskerd29ffbzjdc8d1lhcaoa9camw3

 

[Fluff]

If you're still having problems, here is a non-array formula that does the same thing
=SUMPRODUCT(--ISNUMBER(SEARCH(F1:F28,A1)))

 

[lyman]

If you're still having problems, here is a non-array formula that does the same thing
=SUMPRODUCT(--ISNUMBER(SEARCH(F1:F28,A1)))

In my excel workbook I have tried the array formula with SUM and the non array formula with SUMPRODUCT formulas. I have copied formulas straight into my excel workbook to make sure I'm doing exactly what you have done. The formula is not picking up more than 5 EU countries. When I test I see the formula doesn't pick up countries like Austria but picks up Germany France, UK every time. It's weird. Could it have something to do with my settings in excel?

I downloaded Mark's workbook and in that file it works fine. There is no difference between how both files are set up but the results are different. I'm bemused.

 

[Fluff]

Double check that your countries do not have leading/trailing spaces.
Also where did you get the list of countries from? did you copy & paste them from a website?

 

[lyman]

Double check that your countries do not have leading/trailing spaces.
Also where did you get the list of countries from? did you copy & paste them from a website?

I had initially formatted text with format painter as I thought that the text might be an issue. However now I deleted column and wrote out the countries manually. It works perfectly now. Thanks!!!

 

[Fluff]

Glad it's working & thanks for the feedback

 

[MARK858]

@lyman, if you get the situation again then there are other options to clean up your data without rewriting everything either by VBA or a formula like

=TRIM(CLEAN(SUBSTITUTE(F1,CHAR(160)," ")))

which will take care of most issues.