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"

 

[spbcson]

Sorry This is it:

Cell A: f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227
Cell B (with function): ISALIA ZARRASON

 

[Rick Rothstein]

Sorry This is it:

Cell A: f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227
Cell B (with function): ISALIA ZARRASON

Have you read either one of my last two messages???

 

[spbcson]

Sorry Rick I was try´n pgc01, Your UDF works perfectly. Thank you Very Much

 

[spbcson]

Have you read either one of my last two messages???

How can I get from the string:

f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227

Los Cerritos?

That part of text always come with the first letter in upper case

Regards

 

[Rick Rothstein]

How can I get from the string:

f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227

Los Cerritos?

That part of text always come with the first letter in upper case

Give this UDF a try...

Function ProperCaseWords(S As String) As String
  Dim X As Long, TempText As String, Words() As String
  TempText = S
  For X = 1 To Len(TempText)
    If Mid(TempText, X, 1) Like "[!A-Za-z ]" Then Mid(TempText, X) = " "
  Next
  Words = Split(Application.Trim(TempText))
  For X = 0 To UBound(Words)
    If Words(X) <> StrConv(Words(X), vbProperCase) Or Len(Words(X)) = 1 Then Words(X) = ""
  Next
  ProperCaseWords = Application.Trim(Join(Words))
End Function

 

[spbcson]

Give this UDF a try...

Function ProperCaseWords(S As String) As String
  Dim X As Long, TempText As String, Words() As String
  TempText = S
  For X = 1 To Len(TempText)
    If Mid(TempText, X, 1) Like "[!A-Za-z ]" Then Mid(TempText, X) = " "
  Next
  Words = Split(Application.Trim(TempText))
  For X = 0 To UBound(Words)
    If Words(X) <> StrConv(Words(X), vbProperCase) Or Len(Words(X)) = 1 Then Words(X) = ""
  Next
  ProperCaseWords = Application.Trim(Join(Words))
End Function

It worked perfect
Again Thank You Rick

 

[spbcson]

It worked perfect
Again Thank You Rick

How can I use both UDF in the same worksheet?

 

[olives]

It worked perfect. Again Thank You Rick

Rick, you're the man! Just used this myself. Thank you!

 

[vandelfin]

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)

Thank you very much for this post!
It was very helpful and has saved me several hours of work!

Regardsn

 

[AjayS]

Hi Rick/Team

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"