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]

All words always together like the example

 

[pgc01]

For ex., a udf solution:

Function GetUpW(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = ".*?([A-Z]{2,}(\s+[A-Z]{2,})*).*"
    GetUpW = .Replace(s, "$1")
End With
End Function

In B1:

=GetUpW(A1)


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">//-- y MICROSOFT EXCEL computer software June 23489</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">MICROSOFT EXCEL</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">I like to work with the ADOBE ACROBAT READER sometimes</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">ADOBE ACROBAT READER</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[spbcson]

Since you can have other upper case text (the J in June for example) that you do not want to retrieve, you will need to give us more details about the text you want to retrieve. Will, for example, the upper case text you want to retrieve always start as the location where the first upper letter appears (as shown in your example)? If not, can you give us some other criteria to identify the text?

This is the original text string

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

From that I only need ISALIA ZARRAZON DE GOICOCHEA CEBORCA, there is no a logical sequence for the position of the words in upper case in a list

 

[spbcson]

It worked but I get only Two words.
cell A cell b( with function)
f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227 ISALIA ZARRASON

 

[spbcson]

For ex., a udf solution:

Function GetUpW(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = ".*?([A-Z]{2,}(\s+[A-Z]{2,})*).*"
    GetUpW = .Replace(s, "$1")
End With
End Function

In B1:

=GetUpW(A1)

	A	B	C
1	//-- y MICROSOFT EXCEL computer software June 23489	MICROSOFT EXCEL
2	I like to work with the ADOBE ACROBAT READER sometimes	ADOBE ACROBAT READER
3
[Book1]Sheet1

<tbody>
</tbody>

It worked but I get only Two words.
cell A cell b (with function)
f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227 ISALIA ZARRASON

 

[Rick Rothstein]

This is the original text string

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

From that I only need ISALIA ZARRAZON DE GOICOCHEA CEBORCA, there is no a logical sequence for the position of the words in upper case in a list

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)

 

[spbcson]

Sorry it is like this:

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

Cell B (with function): ISALIA ZARRASON

 

[spbcson]

Sorry it is like this:

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

Cell B (with function): ISALIA ZARRASON

 

[spbcson]

Thanks it worked but only two words:

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

 

[Rick Rothstein]

Thanks it worked but only two words:

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

You don't quote anything from the message you are answering, so it is hard to know who you are replying to. In the case of the function I posted in Message #16, your statement is incorrect... the function returns all upper case words of 2 or more characters. If you haven't already done so, give it a try.