Case sensitive formula

[Jeff H]

In a glossary I’ve made, I have the following array formula to count the instances of a search term I enter in cell B1: {=SUM(LEN(WordList)-LEN(SUBSTITUTE(WordList,B1,"")))/LEN(B1)}

The glossary table is the range “WordList”. The formula works great, except that it's case sensitive. Is there a way to make it not case sensitive?

I tried putting =UPPER(B1) in cell G1 and changing the formula to: {=SUM(LEN(WordList)-LEN(SUBSTITUTE(UPPER(WordList),G1,"")))/LEN(G1)}, but then it counts the search term itself, in cell B1, which is not in WordList (currently A3:D58).

As a secondary, follow up question, how does this formula work? I forget where I got it from, but I can’t figure out the logic involved.

[HansV]

You can use the following array formula (confirmed with Ctrl+Shift+Enter:

=SUM(LEN(UPPER(WordList))-LEN(SUBSTITUTE(UPPER(WordList),UPPER(B1),"")))/LEN(B1)

To explain the formula I'll use the original case-sensitive version. Let's say that A3 (one of the cells in WordList) contains the phrase "The Sun and The Moon", and that the search term in B1 is "The".

A3 contains 20 characters, so LEN(A3) = 20
SUBSTITUTE(A3,B1,"") replaces all occurrences of "The" in the value of A3 with the empty string "", effectively removing those occurrences. The result is " Sun and Moon" (With two spaces between "and" and "Moon", but the browser doesn't show that).
The length of the result is 14, so LEN(SUBSTITUTE(A3,B1,"")) = 14.
The difference is 20 - 14 = 6. Removing all occurrences of "The" has shortened the string by 6 characters.
The search term "The" is 3 characters long: LEN(B1) = 3.
The number of times the search term occurs in A3 is 6 / 3 = 2.

Applying SUM adds this for all cells in WordList, so you get the total number of occurrences.

[Jeff H]

Very cool!! Thanks Hans!

[HansV]

I have moved this thread from the Word forum to the Excel forum because it is about Excel.
(Thanks to Macropod for alerting me)

[Jeff H]

I have moved this thread from the Word forum to the Excel forum because it is about Excel.
(Thanks to Macropod for alerting me)

Oops! Sorry I about that.