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)
))

DOWNLOAD WORKSHEET

To learn more about creating LAMBDA functions see below blog

list of blog posts

Also below video