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"

 

[Rick Rothstein]

Is it possible to get the Numbers along with Upper case.
For example is a String have "MICROSOFT EXCEL2013 offers a great help.", Result should be - "MICROSOFT EXCEL2013"

To allow numbers, just add the 0-9 that I show in red below...

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[B][COLOR="#FF0000"]0-9[/COLOR][/B] ]" 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

 

[paulpaul50]

Thank you so much Rick!!

what about the opposite? How can i exclude returning Uppercase that contains numbers?
For example string "MICROSOFT EXCEL2013 Special Edition", Result should be "MICROSOFT"

Thank you in Advance

 

[Rick Rothstein]

Thank you so much Rick!!

what about the opposite? How can i exclude returning Uppercase that contains numbers?
For example string "MICROSOFT EXCEL2013 Special Edition", Result should be "MICROSOFT"

See Message #16

 

[paulpaul50]

I input the function you mentioned from message #16 in however it does not exclude part with numbers
Text: "bracelet JASSY COURT ver. YJRU0055GOU gold"
the function below returns: "JASSY COURT YJRU GOU"
Result that I am looking for is: "JASSY COURT"


Function UpperCaseWordsnonum(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

 

[paulpaul50]

typo in the last post*

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

 

[Rick Rothstein]

Ah, I see what you mean. Just to point out, your upper case words request is different from the OP of this thread. With that said, I think you will have to help define what a "word" is. For example, is one-two (words with dashes between them) a single word, two independent words or not a word at all? Same question about one_two and one.two and one/two and one:two and probably others.

 

[Rick Rothstein]

Ah, I see what you mean. Just to point out, your upper case words request is different from the OP of this thread. With that said, I think you will have to help define what a "word" is. For example, is one-two (words with dashes between them) a single word, two independent words or not a word at all? Same question about one_two and one.two and one/two and one:two and probably others.

When you respond to my questions above, please ignore the fact that I forgot to make the words upper case... I am interest in the "structure" for the questions I asked, not the fact that I forgot to upper case them.

 

[ndev2k]

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)

Hi Rick,

This has saved me some considerable time in my work but I have a slight problem. Sometimes I deal with the following name lists.

Neil DEVLIN
Bob POTTER
Peter PARKER
John G
Paul TURNER
Jimmy Y
Marc VAN DER MEIJER


I have been using

=TRIM(RIGHT(SUBSTITUTE(A22," ",REPT(" ",100)),100))

In order to return the last word because I need the surnames which are always given in capitals.

As you can see this doesn't work for the last in the list so I looked and found your UDF which works great. However it doesn't pick up the ones in the list that have only provided their last initial which I also need to grab.

Is there are way to do an IF statement somewhere in VB to say if the UDF doesn't return anything then fall back to the TRIM statement I used.

Not great with VB so struggling to implement it.

Thanks

 

[István Hirsch]

You can use a formula. Is there only one first name?

 

[ndev2k]

You can use a formula. Is there only one first name?

No there isn't, sometimes their middle name is included but the Surname is always in CAPITALS.