Here are 3 methods you can use to count uppercase lowercase or numbers in a string.

## Using a Formula

**=SUM(ISERROR(MID(B3,SEQUENCE(LEN(B3)),1)+0)*EXACT(LOWER(MID(B3,SEQUENCE(LEN(B3)),1)),MID(B3,SEQUENCE(LEN(B3)),1)))**

=SUM({0;0;0;1;1;1;0;0}*{1;1;1;0;1;1;1;1})… during multiplication the TRUE & FALSE values from the two arrays convert to 1 & 0

To count UPPERCASE letters…just change the LOWER function to the UPPER function as shown below

**=SUM(ISERROR(MID(B3,SEQUENCE(LEN(B3)),1)+0)*EXACT(UPPER(MID(B3,SEQUENCE(LEN(B3)),1)),MID(B3,SEQUENCE(LEN(B3)),1)))**

For counting numbers, this is a bit simpler,

**SUM(–ISNUMBER(MID(B3,SEQUENCE(LEN(B3)),1)+0)**)

**ISNUMBER(MID(B3,SEQUENCE(LEN(B3)),1)+0)** results to an array of TRUE & FALSE {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}

Convert this to 1& 0 using the double unary method **–ISNUMBER(MID(B3,SEQUENCE(LEN(B3)),1)+0)** resulting to {1;1;1;0;0;0;1;1}

Thanks to Rick Rothstein comment on this Linkedin post we can shorten the above formula by using the ASCII (* American Standard Code for Information Interchange*) table.

To count lowercase letters

**=SUMPRODUCT(–(CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))>96))**

**How the function works**

CODE function returns the Decimal Value of characters in a string.

**CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1)** results to **{49;50;51;77;106;101;53;55}**

Since lowercase letters have between 97 and 122 decimal values, check for anything above 96

**CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))>96** results to **{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}**

Convert the TRUE/FALSE to 1/0 using the double unary method

**–(CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))>96)** result to **{0;0;0;0;1;1;0;0}**

SUMPRODUCT just sums up this array** =SUMPRODUCT({0;0;0;0;1;1;0;0})**=2

NB: Above function only works if you do not have symbols in your string. If you have symbols then you have to check both the upper limit decimal value 122 and lower limit value 97

**=SUMPRODUCT((CODE(MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1))>97)*(CODE(MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1))<123))**

To count UPPERCASE letters

Simplify the function further below

**=SUMPRODUCT(–(ABS(CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))-77.5)<12.5))**

Where do the 77.5 and 12.5 come from?

=SUMPRODUCT(–(ABS(CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))-**(65+90)/2**)<**(90-65)/2)**)

**ABS(X-(Min+Max)/2) < (Max-Min)/2 is the same as AND( X>=Min, X<=Max )**

The upper limit decimal value for UPPERCASE letters is 90 and the lower limit value is 65

(**65+90)/2**=77.5 and **(90-65)/2)**=12.5

To count numbers

**=SUMPRODUCT(–(ABS(CODE(MID(B3,ROW(INDIRECT(“1:”&LEN(B3))),1))-52.5)<4.5))**

## Using Power Query

**Text.Length(Text.Select([PROJECT NUMBER],{“a”..”z”}))**

## Using LAMBDA function

To count lowercase text in a string use below

**=LAMBDA(string,LET(chars,MID(string,SEQUENCE(LEN(string)),1),a,ISERROR(chars+0),b,EXACT(LOWER(chars),chars),SUM(a*b)))**

Thanks again to Rick Rothstein comment on this Linkedin post we can shorten the above LAMBDA to

**=LAMBDA(string,LET(chars,ABS(CODE(MID(string,SEQUENCE(LEN(string)),1))-109.5)<=12.5,SUM(chars+0)))**

Where are we getting the 109.5 and 12.5?

From the ASCII table decimal values. The upper limit decimal value for lowercase letters is 122 and the lower limit value is 97

**(122+97)/2=109.5** and **(122-97)/2=**12.5

Create a named range based on the above LAMBDA based on a range

To count UPPERCASE text in a string use below

**=LAMBDA(string,LET(chars,MID(string,SEQUENCE(LEN(string)),1),a,ISERROR(chars+0),b,EXACT(UPPER(chars),chars),SUM(a*b)))**

Or

**=LAMBDA(string,LET(chars,ABS(CODE(MID(string,SEQUENCE(LEN(string)),1))-77.5)<=12.5,SUM(chars+0)))**

To count NUMBERS in a string use below

**=LAMBDA(string,LET(chars,MID(string,SEQUENCE(LEN(string)),1),a,ISNUMBER(chars+0)+0,SUM(a)))**

Or

**=LAMBDA(string,LET(chars,ABS(CODE(MID(string,SEQUENCE(LEN(string)),1))-52.5)<=4.5,SUM(chars+0)))**

To learn more about creating LAMBDA functions see below blog

Also below video

Thank you for referencing my LinkedIn postings. So that your readers fully understand the relationship in my posting at LinkedIn and which you used above, a number X is between two other numbers A and B this relationship is true…

(ABS(X-((A+B)/2))<ABS(B-A)/2

(use <= instead of < if the endpoints are included in the test). In words, a number is located between two other numbers if the distance from that number to the average of the two other numbers is less than half the distance between those two other numbers. Now, this looks long, but if A and B are constants, substituting those constants and performing the math condenses the relationship down to a rather small size.