Excel VBA – Find UpperCase Word in String or Worksheet

Finding Uppercase Words in String?

Use this direct method to find out whether any lengthy string has words with all Caps.

For example: This line has one WORD as all caps.

If a word has all letters in caps, then it might denote some important keyword. In that case, to find it, use this tiny code.

Sub Find_All_Capital_Word()
    Dim iStr As String, iArr As Variant, idx As Double
    
    iStr = ThisWorkbook.Sheets(1).Cells(1, 1)
    iStr = VBA.Replace(iStr, "  ", " ", , , vbTextCompare)
    iArr = VBA.Split(iStr, " ", , vbTextCompare)
    
    For idx = 0 To UBound(iArr)
        If iArr(idx) = VBA.UCase(iArr(idx)) Then
            ThisWorkbook.Sheets(1).Cells(idx + 1, 2) = iArr(idx)
        End If
    Next
    
End Sub

The above code gets the sentence in a string variable. Then splits it into an array of variables that hold each word. Now each word is verified whether it is equivalent to its own replica of all capital letters.

If there is a match found, then it is reported as a word with all capital letters.