30 November, 2021

How to count any characters and digits in a text string using ARRAY formula

Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.

Here is an example of a string located in cell A2:

Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.

I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here:

The array formulas shown on the right side above are underlying the results shown in column B. If there are any digits in your string, they can be counted too, if entered and formatted in column A as text(!).

The formula used here is not case-sensitive, so counting includes both lower- and upper-case characters (like s and S in the example above).

Reminder: the ARRAY formulas have to be entered - after typing them in - using the keyboard sequence CTRL+SHIFT+ENTER.


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.