Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Counting number of text strings in a cell

  1. #1
    New Member
    Join Date
    Aug 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Counting number of text strings in a cell

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,078
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Counting number of text strings in a cell

    How about

    Excel 2013/2016
    ABCDEF
    1Australia,China,Germany,France,South Africa,Spain3UK
    2Germany
    3France
    4Spain
    5Belgium

    Sheet1



    Array Formulas
    CellFormula
    B1{=SUM(--ISNUMBER(SEARCH(F1:F5,A1)))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Counting number of text strings in a cell

    Fluff beat me to it.
    Last edited by mumps; May 17th, 2019 at 04:07 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of text strings in a cell

    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 by Dr. Demento; May 17th, 2019 at 04:10 PM.

  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of text strings in a cell

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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,078
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Counting number of text strings in a cell

    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)))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of text strings in a cell

    Quote Originally Posted by Fluff View Post
    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.

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,230
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Counting number of text strings in a cell

    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?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Aug 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of text strings in a cell

    Quote Originally Posted by MARK858 View Post
    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.

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,230
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Counting number of text strings in a cell

    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •