TrickJarrett.com

Counting Character Appearances in Excel

8/9/2022 12:53 pm |

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"A","")))

The above is how you count the number of times the letter "A" appears inside the contents of cell A1 in Excel. I don't know if there is a better way, but this was how I came to do it when I was working on something in Excel this morning and felt like sharing.

Essentially what it does is take the total length of the word, then takes the word and removes every "A" in it, and then compares the length difference to determine how many As it removed.

N.B. - Capitalization matters here. You could modify it to capture lowercase 'a's as well, however for my purposes I just used UPPER() to make the words I was checking all uppercase to do these counts.

📧 Newsletter

Did you know that you can sign up for the email newsletter of this blog? Get an email with the day's posts delivered to your inbox! Sign up here.