Counting number of text strings in a cell

lyman

New Member
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

MrExcel MVP, Moderator

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

mumps

Well-known Member
Fluff beat me to it.

Last edited:

Dr. Demento

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

Code:
``=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1``
Never mind - I guess I didn't look at your example close enough. Go with Fluff!!

Last edited:

lyman

New Member
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

MrExcel MVP, Moderator
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

New Member
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

MrExcel MVP
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

New Member
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

MrExcel MVP
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.