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

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Australia,China,Germany,France,South Africa,Spain	3				UK
2						Germany
3						France
4						Spain
5						Belgium
Sheet1

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

 

[mumps]

Fluff beat me to it.

 

[Dr. Demento]

If each country is separated by a delimiter (in this case a comma), this formula will count the number of delimiters plus 1.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1

Never mind - I guess I didn't look at your example close enough. Go with Fluff!!

 

[lyman]

Thanks Fluff
I did what you proposed and made sure to get the curly brackets using ****+Ctrl+Enter. The formula however seems to ever only return between 0 and 2 when I know that can't be correct. I have multiple rows of data so I double clicked the cell to auto fill all rows with same formula. I can't work out why I'm getting, 0,1, or 2 in the cells.

In each cell have a large number of countries, here's an example of cell contents:

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,Kosovo,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

 

[Fluff]

If you are filling the formula down you need to use absolute references to the lookup table like
=SUM(--ISNUMBER(SEARCH($F$1:$F$6,A1)))

 

[lyman]

If you are filling the formula down you need to use absolute references to the lookup table like
=SUM(--ISNUMBER(SEARCH($F$1:$F$6,A1)))

Yup, that's what I'm doing. Here is the formula: {=SUM(--ISNUMBER(SEARCH('EU Countries'!$A$1:$A$28,T2)))}
Even in the first formula cell I don't get the accurate number.

 

[MARK858]

What number are you expecting with the 5 countries Fluff had in F1:F5 in post number 2 with your data from post number 5?

 

[lyman]

What number are you expecting with the 5 countries Fluff had in F1:F5 in post number 2 with your data from post number 5?

In my example above there are 20 EU countries. I replicated Fluff's spreadsheet in my own to check the formula and it works for the 5 countries he uses. However when I tried the same formula to check a cell with all the countries I copied above it returned a result of 5. I did remember to change the range of column F to F1:F28 for all the European countries and then use ****+Ctrl+Enter.

 

[MARK858]

Can you copy and paste your 28 countries in the thread (in a single vertical format as if you were pasting them in a column in excel) so we can test.

When I do it with the formula

=SUM(--ISNUMBER(SEARCH($F$1:$F$28,A1)))

I get 28 with my list.