Formula to find the upper case in the sentence as true

Question

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find.

I tried using the below formula, but this formula give the result only when the uppercase is present in right.

=IF(CODE(RIGHT(C3,1))>96,"lower","UPPER")

enter image description here

score 2 · Accepted Answer · edited Mar 20 '17 at 10:16

Your question isn't clear, but this should get you going

Sub AvoidThePirates()

Dim startRow As Integer
startRow = 2    ' EDIT THIS ME HEARTIES

Dim columnToLookUp As String
columnToLookUp = "C" ' Arrrgh ye scurvy sea dog

Dim columnResults As String
columnResults = "D" ' Show me ya gold

'Touch below this and I'll feed ya to the sharks

Dim isUpper As Boolean


Do While (Range(columnToLookUp & startRow).Value <> "")

    Dim hasUpper As Boolean
    hasUpper = False

    isUpper = False

    Dim valueToCheck As String
    valueToCheck = Range(columnToLookUp & startRow).Value
    valueToCheck = Replace(valueToCheck, ".", "")

    Dim i As Integer

    For i = 0 To Len(valueToCheck)
        Dim chara As String
        chara = Mid(valueToCheck, i + 1, 1)
        If IsLetter(chara) Then
           If StrComp(chara, UCase(chara), vbBinaryCompare) = 0 Then
                Dim charaB As String
                charaB = Mid(valueToCheck, i + 2, 1)
                If IsLetter(charaB) Then

                   If StrComp(charaB, UCase(charaB), vbBinaryCompare) = 0 Then
                       isUpper = True
                        Exit For
                   End If

                End If
           End If
        End If
    Next i

    If (isUpper) Then
    Range(columnResults & startRow).Value = "UPPER"
    Else
    Range(columnResults & startRow).Value = "lower"
    End If


    startRow = startRow + 1
Loop


End Sub


Function IsLetter(strValue As String) As Boolean
    Dim intPos As Integer
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function

Before

enter image description here

After VBa run

enter image description here

Also see How do I add VBA in MS Office?

Formula to find the upper case in the sentence as true

1 Answers1