The typical way to do this is to use a formula based on theSUBSTITUTE functionand theLEN function.
LEN Function
The LEN function calculates the number of characters in a text string.
SUBSTITUTE Function
The SUBSTITUTE function performs substitutions in a text string.
Notice the LEN function is used twice.
The first LEN calculates the length of the original text string.
Next, the result from the second LEN is subtracted from the result from the first LEN.
This means an uppercase “A” will not be counted.
See below for an option that is not case-sensitive.
Case-insensitive option
SUBSTITUTE is a case-sensitive function, so it will match the case when replacing text.
The result is returned to SUBSTITUTE and the formula then runs as before.
All uppercase A’s in the text are now lowercase, so theywillbe included in the count.
Both options will return the same result.
LEN will also count characters in numbers, but number formatting is not included.
The Excel SUBSTITUTE function replaces text in a given string by matching.
Numbers, punctuation, and spaces are not affected.
