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
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?
For ex., a udf solution:
Code: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>
This is the UDF (user defined function) that I came up with...This is the original text string
f r^ ISALIA ZARRASON' DE GOICOCHEA [ 1 ^'zr'x''—Los Cerritos 1 V CEBORCA 25227From 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
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
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.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