How to Get all uppercase characters from cell in excel?

Question

I want all capital letters .. JUST LIKE, Casey Richard Omar W= CROW ,...
Processing of Dimensional Stone = PDS

like all capital letters or shortname

Answer 1

You could use Regular Expressions to do this

Sub ExampleRegExp()
    Dim RegExp As Object
    Dim str As String

    str = "Casey Richard Omar W"

    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Global = False
        .IgnoreCase = False
        .Pattern = "([a-z]+)|(\s)"

        Do While .test(str) = True
            str = .Replace(str, vbNullString)
        Loop

        MsgBox str
    End With
End Sub

Or using it as a UDF

Function GetUpperCase(str As String) As String
    Dim RegExp As Object

    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Global = False
        .IgnoreCase = False
        .Pattern = "([a-z]+)|(\s)"

        Do While .test(str) = True
            str = .Replace(str, vbNullString)
        Loop

        GetUpperCase = str
    End With
End Function

in A2 I've entered =GetUpperCase(A1)