Counting number of text strings in a cell

lyman

New Member
Joined
Aug 23, 2013
Messages
19
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
How about


Excel 2013/2016
ABCDEF
1Australia,China,Germany,France,South Africa,Spain3UK
2Germany
3France
4Spain
5Belgium
Sheet1
Cell Formulas
RangeFormula
B1{=SUM(--ISNUMBER(SEARCH(F1:F5,A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Fluff beat me to it. :)
 
Last edited:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top