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]

I'm not 100% sure of the construction of all the possible text that would need to be processed, but this modification to my code (note that I changed the function's name) works for the two examples you posted...

Function UpText(S As String) As String
  Dim X As Long, TempText As String, Arr() As String
  TempText = " " & S & " "
  For X = 2 To Len(TempText) - 1
    If Mid(TempText, X, 1) Like "[!A-Z0-9.&]" Or Mid(TempText, X - 1, 3) Like "[!A-Z0-9.&][A-Z0-9.&][!A-Z0-9.&]" Then Mid(TempText, X) = " "
  Next
  Arr = Split(Application.Trim(TempText))
  For X = 0 To UBound(Arr)
    If Not Arr(X) Like "*[!0-9.]*" Then Arr(X) = ""
  Next
  UpText = Application.Trim(Join(Arr))
End Function

 

[irzam07]

Wow its works like champ. Thanks a lot Rick, it was exactly that I was searching for!

 

[Peter_SSs]

A more direct approach UDF that you could also consider. Note also this returns different results to the UpText UDF depending on the particular data that you may or may not encounter and the exact results that you would expect (see rows 3 & 4 below). (I know that you didn't mention the "-" as a possibility but I wondered if that may be possible.)
What result would you want if A4 of my sample data is possible?

Function UCtext(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^ ]*[^A-Z\.& \-][^ ]*(?= |$)"
    UCtext = Application.Trim(.Replace(s, ""))
  End With
End Function

irzam07.xlsm
	A	B	C
1	Receive 10000 P.I.E INDUSTRIAL at 3.220 per lot	P.I.E INDUSTRIAL	P.I.E INDUSTRIAL
2	Send 2200 D&O GREEN TECH at 5.900 per lot	D&O GREEN TECH	D&O GREEN TECH
3	Send 100 X BOX INDUSTRIAL at 35 per lot	X BOX INDUSTRIAL	BOX INDUSTRIAL
4	Send 100 X-BOX INDUSTRIAL at 35 per lot	X-BOX INDUSTRIAL	BOX INDUSTRIAL
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=UCtext(A1)
C1:C4	C1	=UpText(A1)

 

[Rick Rothstein]

Note also this returns different results to the UpText UDF depending on the particular data that you may or may not encounter

I deliberately excluded single upper case letters in case there was text like this (completely made up example)...

Send A 4 X 6 Photo Of 100 X-BOX INDUSTRIAL At 35 Per Lot

I also completely qualified my response as to the type of data that the function was to process because I felt two examples were not really enough to go on.

 

[Peter_SSs]

I meant no criticism at all of your results Rick, just offering an alternative, & prodding again about possible different data.

 

[Rick Rothstein]

Peter... I did not take your post as a criticism, rather, I thought it might be useful to the OP to understand why I wrote my code the way I did and where your suggestion might cause a problem if the general text strings he had to deal with could have single upper case letters elsewhere in the text. My last sentence was really meant for the poster and not you per se... two examples is really not enough to form an answer for that one can be confident in.

 

[Peter_SSs]

two examples is really not enough to form an answer for that one can be confident in.

Agreed.

 

[JEC]

@Peter, nice Regex! I was wondering, is the part in red necessary? It seems to work without aswell. Just curious!

[^ ]*[^A-Z\.& \-][^ ]*(?= |$)

 

[Peter_SSs]

is the part in red necessary?

Yes, without it you would get this result

irzam07.xlsm
	A	B
5	Send 100 abC aND cO	C ND O
Sheet1

Cell Formulas
Range	Formula
B5	B5	=UCtext(A5)

 

[JEC]

Thanks! Clear?