-5

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

Kiran Sakaray
  • 33
  • 8
  • How many uppercase words? Are they in any particular order? Did you try the formula you put? – Raystafarian Dec 19 '14 at 13:00
  • Yes i tried with the formula, minimum 1 & max 8 uppercase wold. – Kiran Sakaray Dec 19 '14 at 13:06
  • Why is cell C4 classed as lower when it has upper case values? Also, look at C8 - what should this be, lower or upper? – Dave Dec 19 '14 at 13:14
  • 1
    The cell C4 ends with "Policy" letter "y" is a lowercase, so it is showing as lower. Cell C8 has a uppercase word in between the sentence, i want this to be shown as upper result. – Kiran Sakaray Dec 19 '14 at 13:19
  • This is going to be very difficult to achieve with a formula, as far as I can tell. VBA would be the choice. – Raystafarian Dec 19 '14 at 13:26
  • Kiran, when you reply, use the at symbol so they know you've replied to them, eg if you write @Dave then I will get a notification :) – Dave Dec 19 '14 at 13:30
  • 1
    Can I ask, is your logic *actually* if anywhere within the entire string has more than 2 characters beside each in upper case, then show the result UPPER? I mean, what should C17 show? Upper or lower? – Dave Dec 19 '14 at 13:31

1 Answers1

2

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?

Community
  • 1
Dave
  • 25,297
  • 10
  • 57
  • 69
  • Yes Dave, i want the 2 char uppercase beside should show UPPER. this is working for me, but few it is capturing the as UPPER when the Uppercase is not avilable. like : 1. deal notes/ questions + answers/ uw/ u/w notes/ firm called 2. quote notes/ Cancel Deal/ ReActivate Deal/ deal notes/ clerk called 3. Deal notes/ question - lawyer/ answer - lawyer – Kiran Sakaray Dec 19 '14 at 14:50
  • @KiranSakaray, out of the examples you just showed, versions 1, 2 and 3 should all of them be upper or lower? – Dave Dec 19 '14 at 15:00
  • sorry, all of them should be lower – Kiran Sakaray Dec 19 '14 at 15:05
  • Because their is no 2 continues uppercase in this examples, but still the result is UPPER – Kiran Sakaray Dec 19 '14 at 15:06
  • @KiranSakaray, I've updated the code, please try now] – Dave Dec 19 '14 at 15:16
  • That's working fine for all the cells Dave. I think some problem with the special characters, as the below 2 examples are still showing UPPER, it should be LOWER 1. questions - lawyer/ questions - purchaser's lawyer/ docs requested/ answer - purchaser's lawyer/ ./ docs rec'd 2. ../ questions - lawyer/ Deal notes/ answers - lawyer – Kiran Sakaray Dec 19 '14 at 15:30
  • Fantastic you made my day, it was taking 3 hrs me to separate the UPPER & lower case. Now I can run the macro in 4 mins. – Kiran Sakaray Dec 19 '14 at 15:51
  • Pleasure @KiranSakaray, glad it is working for you – Dave Dec 19 '14 at 15:53