1

I would like to parse through URLs and count how many times a string e.g. 'apple' occurs within a range. I tried =COUNTIF but it's looking for exact matches.

I have about 80k rows of URLs and I need to count how many times 'apple', for example, appears within those URLs within the range. The same string can appear more than once, and I need to count all occurrences (not just cells containing one or more occurrences).

2
  • By "a range", I assume you mean multiple cells? If yes, can the string appear more than once in any given cell? Jun 3, 2019 at 15:51
  • Yes. I have about 80k rows of urls and I need to count how many times 'apple' appears within those URLs within the range. The same string can appear more than once.
    – neo2049
    Jun 3, 2019 at 15:53

2 Answers 2

0

Assuming you have a recent version of Excel, you can use TEXTJOIN for this:

=(LEN(TEXTJOIN("",TRUE,A1:A100))-LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A1:A100),"apple","")))/LEN("apple")

(note that this formula will include the "apple" in "apples", "pineapple", etc...)

3
  • Thanks for your help. When testing with a small sample it works perfectly. However, when working on the real data I get the following error: A value used in the formula is of the wrong data type. The data type for the column is General. When changing to Text, I still get the same error.
    – neo2049
    Jun 4, 2019 at 9:45
  • This formula should work, but it would be a better answer if you add a few sentences to explain how/why it works.
    – fixer1234
    Jun 4, 2019 at 20:35
  • @neo2049, this formula ought to work. If you are running into an error, the only way for people here to figure out why would be to replicate your problem. Please add an example or two to the question where this formula isn't working for you.
    – fixer1234
    Jun 4, 2019 at 20:37
0

It can be done with CountIf if you use the wildcard criteria '*'. apple will search for anything that contains apple including pineapple. If you do apple* it will pull apple, apples but not pineapple.

As requested, I'm expanding on my response.

=Countif(A:A,"*apple*")

An example:

Column A
www.apples.com
www.apple.ca
www.pizza.com
www.pineapples.com

*apple* = 3
4
  • As-is, this is more of a hint than a working solution (so more like a comment). Can you expand this answer with an actual formula? An example that demonstrates the result would be even better (verify that it counts every occurrence rather than the cells). :-)
    – fixer1234
    Jun 4, 2019 at 1:37
  • Thanks for the follow-up, it was because they had already used CountIF, I didn't think I needed to go into further detail but I'm happy to :) If needed I can add a screenshot of my workbook that I tested this in.
    – Carrie
    Jun 4, 2019 at 19:38
  • The question is actually total occurrences, which can be in multiple cells and multiple times within a cell (it was originally clarified in a comment, so you might have missed it). If you check your formula against cases with multiple occurrences in a cell, you'll find that it counts cells as long as there is one or more occurrences. So this is now a good quality answer, but it doesn't satisfy that requirement.
    – fixer1234
    Jun 4, 2019 at 20:27
  • Thanks, I did miss the clarified content.
    – Carrie
    Jun 4, 2019 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .