VBA to select all capital words in Excel RRS feed

  • Question

  • Hi!

    I'm using the following VBA copied from somewhere in the web to idetify all words in a text string in Excel:

    Function LastName(S As String) As String
      Dim X As Long
      For X = 1 To Len(S)
        If Mid(S & " ", X, 2) Like "[A-Z][A-Z' -]*" Then
          If Mid(S, X) = UCase(Mid(S, X)) Then
            LastName = Trim(Mid(S, X))
            Exit For
          End If
        End If
    End Function

    It works fine EXCEPT the text I work with is French and it includes characters with accents, and the VBA stops AFTER it finds such a character. How to solve this little problem?



    Sunday, June 25, 2017 4:32 PM

All replies

  • Hi Garcini,

    I suggest you use Asc function to get Ascii code of this character. You could confirm if the character is a capital character according to this Ascii code. Here is the example.

    If Asc(Mid(S, X, 1)) > 64 And Asc(Mid(S, X, 1)) < 91 Then

         LastName = LastName & Mid(S, X, 1)

    End If

    Best Regards,


    Monday, June 26, 2017 3:01 AM