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"

 

[Ombir]

Rick,

One possible problem that may occur with your code for Hyphenated name like "John V-DAMON".

 

[Rick Rothstein]

Rick,

One possible problem that may occur with your code for Hyphenated name like "John V-DAMON".

That is not a name I would ever expect, but given the OP's latest posts, it might be one that he could expect. The fix to my code to handle that is simple, though...

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
  Next
End Function

Note to Ombir: I noticed you appeared to be already looking at my code in Message #60 while I was posting an edited version of the original code I posted there. The above code without the dash in the first If..Then statement is the edited version of the code I think you actually saw (which also did not handle V-DAMON as a last name, hence the code above).

 

[ndev2k]

That is not a name I would ever expect, but given the OP's latest posts, it might be one that he could expect. The fix to my code to handle that is simple, though...

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
  Next
End Function

Note to Ombir: I noticed you appeared to be already looking at my code in Message #60 while I was posting an edited version of the original code I posted there. The above code without the dash in the first If..Then statement is the edited version of the code I think you actually saw (which also did not handle V-DAMON as a last name, hence the code above).

Thanks very much. This will make my work much easier. I deal with IT requests and the automated request system allows managers to submit account requests for their entire team at once but then the ticket system flattens to a simple text based request. I use a spreadsheet to copy the request and it pulls all the relevant data together in separate fields for ease of viewing and use.

This was the last piece of the jigsaw that was making unnecessary work for me.

Awesome work guys.

 

[ndev2k]

I have put the UDF into the spreadsheet and it works brilliantly and the results are exactly as I need.

The final question is a little off topic but could anyone help.

Is there a way to use a macro to paste in a comma delimited subset of data. I can get it to work using a text file but at the minute I don't get the data via a file.

I currently paste it in manually using the text import wizard as a delimited data with comma separation. Can someone provide the VBA for pasting this in without the manual text import wizard

 

[Rick Rothstein]

Is there a way to use a macro to paste in a comma delimited subset of data. I can get it to work using a text file but at the minute I don't get the data via a file.

I currently paste it in manually using the text import wizard as a delimited data with comma separation. Can someone provide the VBA for pasting this in without the manual text import wizard

I don't understand... you say you "don't get the data via a file", but then you say you say that you "currently paste it in manually using the text import wizard". Where exactly is the data coming from? And can you post some examples of what that data actually looks like?

 

[ndev2k]

It comes from the clipboard. I have got it working now using

 On Error Resume Next
    Range("A2:F18").Select
    Selection.ClearContents
    Range("A2").Select
    ActiveSheet.Paste
    Selection.TextToColumns DataType:=xlDelimited, _
    ConsecutiveDelimiter:=True, Comma:=True
    On Error GoTo 0

 

[aivaras]

This is the UDF (user defined function) that I came up with...

Function UpperCaseWords(S As String) As String
  Dim X As Long, TempText As String
  TempText = " " & S & " "
  For X = 2 To Len(TempText) - 1
    If Mid(TempText, X, 1) Like "[!A-Z ]" Or Mid(TempText, X - 1, 3) Like "[!A-Z][A-Z][!A-Z]" Then
      Mid(TempText, X) = " "
    End If
  Next
  UpperCaseWords = Application.Trim(TempText)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use UpperCaseWords just like it was a built-in Excel function. For example,

=UpperCaseWords(A1)

Rick, thank you for this formula! It works like a charm with English characters. But it fails with UTF-8 characters.

For example:
If I have a string "ĄŽUOLAS STELMUŽĖ Yra Vienas stipriausių", then your formula returns "UOLAS STELMU".
But it should return "ĄŽUOLAS STELMUŽĖ".

How to fix formula so it would recognize UTF-8 characters too?

 

[Rick Rothstein]

Rick, thank you for this formula! It works like a charm with English characters. But it fails with UTF-8 characters.

For example:
If I have a string "ĄŽUOLAS STELMUŽĖ Yra Vienas stipriausių", then your formula returns "UOLAS STELMU".
But it should return "ĄŽUOLAS STELMUŽĖ".

How to fix formula so it would recognize UTF-8 characters too?

Living in the US my whole life, I have never had to deal with UTF-8 characters, so I am sorry, but I do not know how to fix it to work with them.

 

[pgc01]

Hi

Did you try to compare the words directly with UCase()?

Worked OK for the string you posted.

This was a quick test.
Wrote your string in A1: ĄŽUOLAS STELMUŽĖ Yra Vienas stipriausių
Ran the code and in A2 got: ĄŽUOLAS STELMUŽĖ


I tested with:

Sub Test()

Range("A2").Value = KeepUCaseW(Range("A1").Value)
End Sub

Function KeepUCaseW(s As String) As String
Dim v As Variant
Dim j As Long

v = Split(s)
For j = 0 To UBound(v)
    If v(j) <> UCase(v(j)) Then v(j) = ""
Next j
  
KeepUCaseW = Application.Trim(Join(v))
End Function

 

[Fazila]

That is not a name I would ever expect, but given the OP's latest posts, it might be one that he could expect. The fix to my code to handle that is simple, though...

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
  Next
End Function

Note to Ombir: I noticed you appeared to be already looking at my code in Message #60 while I was posting an edited version of the original code I posted there. The above code without the dash in the first If..Then statement is the edited version of the code I think you actually saw (which also did not handle V-DAMON as a last name, hence the code above).

Thanks for this Rick it worked really well for something I am working on. I was wondering can the function be modified to extract proper case words?

I am trying to split a name to surname and forename columns. The surnames are in capitals and the forenames are in proper case, i.e. my data looks something like this:

ADKINS CONQUEST Ryan
PATEL Mohammed Amin

Using your function I have moved the surnames to a column but now need to move the forenames to another column.

Any help with this would be really appreciated.

Thanks