How to find the first instance of a “lower case ch

Question

How to find the first instance of a "lower case character" in a cell using VBA in Excel?
I have tried using ASCII values but that didn't work.

Answer 1

You can use a RegExp in a UDF to avoid looping through each character:

Function FirstLower(strIn As String) as String
Dim objRegex As Object
Dim objRegM As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "[a-z]"
    .ignorecase = False
If .test(strIn) Then
    Set objRegM = .Execute(strIn)(0)
    FirstLower = objRegM.firstindex + 1
Else
    FirstLower = "no match"
End If
End With
End Function

Answer 2

Private Function DeleteLowerCasesLike(InputString As String) As String
    Dim i As Integer

    For i = 1 To Len(InputString)
        If Mid(InputString, i, 1) Like "[a-z]" Then
            InputString = Left(InputString, i - 1) & Mid(InputString, i + 1)
            i = i - 1
        End If
    Next

    DeleteLowerCasesLike = InputString
End Function

Another RegExp solution which needs to addig referance to Microsoft VBScript Regular Expressions 1.0 (In the VBA window, Tools->Referances menu)

Private Function DeleteLowerCasesRegExp(InputString As String)
    Dim RE As New RegExp
    With RE
        .Global = True
        .IgnoreCase = False
        .Pattern = "[a-z]"
        DeleteLowerCasesRegExp = .Replace(InputString, "")
    End With
End Function

And another solution nor Like neither RegExp is used:

Private Function DeleteLowerCasesAsc(InputString As String) As String
    Dim i As Integer

    For i = 1 To Len(InputString)
        If Mid(InputString, i, 1) = Empty Then Exit For
        If Asc(Mid(InputString, i, 1)) >= 97 And Asc(Mid(InputString, i, 1)) <= 122 Then
            InputString = Left(InputString, i - 1) & Mid(InputString, i + 1)
            i = i - 1
        End If
    Next

    DeleteLowerCasesAsc = InputString
End Function

Another solution in which replace function is used:

Private Function DeleteLowerCasesReplace(InputString As String) As String
    Dim i As Integer

    For i = 97 To 122
            InputString = Replace(InputString, Chr(i), "")
    Next

    DeleteLowerCasesReplace = InputString
End Function

Answer 3

I think you want to remove the first part in your string that is in lower case:

Public Function DeletFirstLowerPart(strTemp As String) As String
    Dim strResult As String, i As Long, findLower As Boolean

    strResult = ""
    findLower = False
    For i = 1 To Len(strTemp)
        If (Mid(strTemp, i, 1) Like "[a-z]") Then
            findLower = True
        Else
            If findLower = True Then
                strResult = strResult & Mid(strTemp, i)
                DeletFirstLowerPart = strResult
                Exit Function
            End If
            strResult = strResult & Mid(strTemp, i, 1)
        End If
    Next i
    DeletFirstLowerPart = strResult
End Function

DeletFirstLowerPart("ABCdefGHI") = "ABCGHI"
DeletFirstLowerPart("ABCdefGHIjkl") = "ABCGHIjkl"

Answer 4

Try the following small UDF:

Public Function Findlower(rin As Range) As Long
    Dim v As String, CH As String, i As Long

    Findlower = 0
    v = rin.Text
    L = Len(v)
    For i = 1 To L
        If Mid(v, i, 1) Like "[a-z]" Then
            Findlower = i
            Exit Function
        End If
    Next i
End Function

It will return the position of the first instance of any lower case letter in a string: