Formula to extract upper case words in a text string

[spbcson]

IS THERE A FORMULA TO EXTRACT A UPPER CASE FROM A TEXT STRING IN A CELL? Example in cell A1 i have this text string:

//-- y MICROSOFT EXCEL computer software June 23489​

How can I get only the words "MICROSOFT EXCEL"

 

[István Hirsch]

Give this a try. Enter with Ctrl + Shift + Enter, not just Enter:

=SUBSTITUTE(MID(A2,(MATCH(1,((ABS(77.5-CODE(MID(A2&"Zß",ROW($1:$100),1))))<13)*((ABS(77.5-CODE(MID(A2&"Zß",ROW($1:$100)+1,1))))<13),0)),100),"Zß","")

 

[ndev2k]

Give this a try. Enter with Ctrl + Shift + Enter, not just Enter:

=SUBSTITUTE(MID(A2,(MATCH(1,((ABS(77.5-CODE(MID(A2&"Zß",ROW($1:$100),1))))<13)*((ABS(77.5-CODE(MID(A2&"Zß",ROW($1:$100)+1,1))))<13),0)),100),"Zß","")

That does work. Will try to add this into my work later.

I would still like to be able to do it as part of the UDF though as it is cleaner so if Rick or nayone else has any better solution it would be appreciated also

 

[pgc01]

Hi

For ex.

Paul TURNER
Jimmy Y
Marc VAN DER MEIJER
Mary J BLIGE

What result do you expect for these examples?

 

[Rick Rothstein]

I would still like to be able to do it as part of the UDF though as it is cleaner so if Rick or nayone else has any better solution it would be appreciated also

Give this UDF (user defined function) a try (it assume last names consist of two or more letters)...

' This function assumes the last name, and only the last
' name, is upper case with two or more letters in it.
Function LastName(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) - 1
    If Mid(S, X, 2) Like "[A-Z][A-Z]*" Then
      LastName = Mid(S, X)
      Exit For
    End If
  Next
End Function

 

[Ombir]

Perhaps a Regex alternative for your problem:

Function ExtractCaps(S As String) As String
With CreateObject("VbScript.RegExp")
    .Pattern = "(\b[A-Z]+\s*\b)+$"
    If .Test(S) = True Then
        Set matches = .Execute(S)
        ExtractCaps = matches(0).Value
    End If
End With
End Function

 

[István Hirsch]

Perhaps a Regex alternative for your problem:

Function ExtractCaps(S As String) As String
With CreateObject("VbScript.RegExp")
    .Pattern = "(\b[A-Z]+\s*\b)+$"
    If .Test(S) = True Then
        Set matches = .Execute(S)
        ExtractCaps = matches(0).Value
    End If
End With
End Function

It extracts "E DEVLIN" from "Neil E DEVLIN".

 

[Rick Rothstein]

Perhaps a Regex alternative for your problem:

Function ExtractCaps(S As String) As String
With CreateObject("VbScript.RegExp")
    .Pattern = "(\b[A-Z][B][COLOR="#FF0000"][A-Z][/COLOR][/B]+\s*\b)+$"
    If .Test(S) = True Then
        Set matches = .Execute(S)
        ExtractCaps = matches(0).Value
    End If
End With
End Function

I would suggest you test two adjacent characters for being upper case (see above in red) so that you do not pick up middle initials. While I took the OP at his word that the last name would be all upper case, I see your code specifically test for that whereas my code doesn't. If that full-out test is needed, here is my code modified to reproduce the same output your RegExp code (with the modification shown in red) does...

' This function assumes the last name, and only the last
' name, is upper case with two or more letters in it.
Function LastName(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) - 1
    If Mid(S, X, 2) Like "[A-Z][A-Z]*" Then
      If Mid(S, X) = UCase(Mid(S, X)) Then
        LastName = Mid(S, X)
        Exit For
      End If
    End If
  Next
End Function

 

[Ombir]

It extracts "E DEVLIN" from "Neil E DEVLIN".

Istvan,

I intentionally implemented this because OP said in Post 40 that Surname is always in capital. Although Rick corrected this possible problem but here is slightly different way that should also work fine:

Function ExtractCaps(S As String) As String
With CreateObject("VbScript.RegExp")
    .Pattern = "(\b[A-Z]{2,}\s*\b)+$"
    If .Test(S) = True Then
        Set matches = .Execute(S)
        ExtractCaps = matches(0).Value
    End If
End With
End Function

 

[Ombir]

Rick,

Thanks for updated code. Here is another approach (lightly tested) without regex that doesn't loop through characters and should work faster than your approach:

Function NameLast(S As String) As String
Dim temp As Variant
Dim txt  As String
temp = Split(S)
For i = UBound(temp) To 0 Step -1
    txt = Trim(temp(i))
    If Len(txt) > 1 Then
        If UCase(txt) = txt Then
            output = txt & " " & output
        Else
            Exit For
        End If
    End If
Next
NameLast = output
End Function

 

[István Hirsch]

Istvan,

I intentionally implemented this because OP said in Post 40 that Surname is always in capital. Although Rick corrected this possible problem but here is slightly different way that should also work fine:

Function ExtractCaps(S As String) As String
With CreateObject("VbScript.RegExp")
    .Pattern = "(\b[A-Z]{2,}\s*\b)+$"
    If .Test(S) = True Then
        Set matches = .Execute(S)
        ExtractCaps = matches(0).Value
    End If
End With
End Function

Hi Ombir,

One letter surname? In this structure „Neil E DEVLIN" I would think „E” refers to the middle name and does not belong to the surname.