Counting number of text strings in a cell

lyman

New Member
Joined
Aug 23, 2013
Messages
16
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
Joined
Jun 12, 2014
Messages
33,466
Office Version
365
Platform
Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Australia,China,Germany,France,South Africa,Spain</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">UK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Germany</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">France</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Spain</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Belgium</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">{=SUM(<font color="Blue">--ISNUMBER(<font color="Red">SEARCH(<font color="Green">F1:F5,A1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,599
Fluff beat me to it. :)
 
Last edited:

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
548
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
Joined
Aug 23, 2013
Messages
16
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
Joined
Jun 12, 2014
Messages
33,466
Office Version
365
Platform
Windows
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
Joined
Aug 23, 2013
Messages
16
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
Joined
Nov 12, 2010
Messages
11,711
Office Version
365, 2010
Platform
Windows, Mobile
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
Joined
Aug 23, 2013
Messages
16
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
Joined
Nov 12, 2010
Messages
11,711
Office Version
365, 2010
Platform
Windows, Mobile
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.
 

Forum statistics

Threads
1,084,945
Messages
5,380,731
Members
401,696
Latest member
MDAUD

Some videos you may like

This Week's Hot Topics

Top