I often find that in my SEO work that I need to analyze large sets of data and in particular locating a string snippet that appears within a large string. Also finding the occurrence of the substring in a large pool of data such as many rows or columns.
Here I present a useful excel function called COUNTIF which returns the number of times a string snippet appears in multiples cells containing the larger string. For example, lets say, I have the following table shown..
Excel countif function and how to use it
Here go to an empty cell and type the following function..
A2:A34 refers to the range of cells within the column shown
“*.htm” refers to the snippet string that it needs to locate
This gives me a count of all the matching string which is the string that ends with ‘.htm’ and one will get a count of 28. If you change the snippet text to ‘*.html’, this means locate strings that end with ‘.html’ then the count returned is 3 which are the exact number of occurrences shown in the table above.
There are other ways where you can highlight the string, but more on this later.