Number of upper case letters in a string

[Ceeyee]

If I need to find the number of upper cased letters in a string, do I have to use a loop or there is quicker ways?

Thanks.

 

[T. Valko]

If I need to find the number of upper cased letters in a string, do I have to use a loop or there is quicker ways?

Thanks.

Are you looking for a formula or VBA code? Sounds like you want VBA code.

 

[Ceeyee]

.. VBA code.


Please advise.

 

[T. Valko]

.. VBA code.


Please advise.

OK, I'm sure someone that programs will reply sooner or later.

 

[Marcelo Branco]

Maybe this UDF funtion

Function countUpper(t As String) As Long
 
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = False
        .MultiLine = False
        .Pattern = "[A-Z]"
        countUpper = .Execute(t).Count
    End With
 
End Function

Usage

A B
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=211><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 110pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=147>This is Test1, OK? YES!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>This is Test2, Ok? Yes!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>This is Test3, ok? yes!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR></TBODY></TABLE>

B1
=countUpper(A1)
copy down

HTH

M.

 

[Marcelo Branco]

@T. Valko

Could you provide a formula? I'm curious.

M.

 

[Ceeyee]

Fantastic.
Thank you.

 

[Ron Coderre]

Another VBA option...

Function UCaseCount(strTest As String)
Dim RegExp As Object
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z]"
    .Global = True
    UCaseCount = Len(.Replace(strTest, ""))
End With
End Function

 

[T. Valko]

@T. Valko

Could you provide a formula? I'm curious.

M.

Here are a couple of examples.

This one needs array entered**:

=COUNT(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

You can also use a named string:

• Letters
• Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Then, array entered**:

=COUNT(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),Letters))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

And this normally entered version for upper case characters:

=SUMPRODUCT(--(ABS(CODE(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1))-77.5)<=12.5))

And this normally entered version for lower case characters:

=SUMPRODUCT(--(ABS(CODE(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1))-109.5)<=12.5))

 

[Marcelo Branco]

T. Valko

Tks very much.

One more for my collection

M