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"

 

[didIDo_that]

To my knowledge you would need to create a specialized function in vb using a loop and the chr function.

 

[didIDo_that]

my apologies it's Asc not Chr, the below function will pull MICROSOFT EXCEL in but it will also pull in the J from June. Maybe this is a place you can start.

Function UPPERONLY(a)


Dim t As String


For i = 1 To Len(a)


If Asc(Mid(a, i, 1)) <= 65 Or Asc(Mid(a, i, 1)) <= 90 Then
t = Asc(Mid(a, i, 1)) & t
End If


Next i


UPPERONLY = t


End Function

 

[spbcson]

To my knowledge you would need to create a specialized function in vb using a loop and the chr function.

I´m not a very skillful Excel user (as you see) don´t you have any suggestion using vb (I don´t know how to use it)
Thanks

 

[Rick Rothstein]

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"

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?

 

[didIDo_that]

Also I need to do a slight correction to the vb.

I'll assume that you are using 2007 or later. Press ctrl + F11 to open the VB window. Click on Insert and insert a new module. Paste the code into the module. You can then type the function in a cell within that workbook. If you want the function to work in any workbook at any time that will take a little more explaining, also I would need more information if you don't want the "J" from June.

Function UPPERONLY(a)


Dim t As String


For i = 1 To Len(a)


If Asc(Mid(a, i, 1)) <= 65 Or Asc(Mid(a, i, 1)) <= 90 Then
t = Mid(a, i, 1) & t
End If


Next i


UPPERONLY = t


End Function

 

[Rick Rothstein]

Also I need to do a slight correction to the vb.

I'll assume that you are using 2007 or later. Press ctrl + F11 to open the VB window. Click on Insert and insert a new module. Paste the code into the module. You can then type the function in a cell within that workbook. If you want the function to work in any workbook at any time that will take a little more explaining, also I would need more information if you don't want the "J" from June.

Function UPPERONLY(a)

Dim t As String

For i = 1 To Len(a)

If Asc(Mid(a, i, 1)) <= 65 Or Asc(Mid(a, i, 1)) <= 90 Then
t = Mid(a, i, 1) & t
End If

Next i

UPPERONLY = t

End Function

Our common question about things like the "J" in "June" notwithstanding, I just want to point out that your function removes spaces as well as non-uppercase letters; the OP wrote in his first message...

'How can I get only the words "MICROSOFT EXCEL"'

Note he shows the internal space character as being preserved.

 

[spbcson]

Our common question about things like the "J" in "June" notwithstanding, I just want to point out that your function removes spaces as well as non-uppercase letters; the OP wrote in his first message...

'How can I get only the words "MICROSOFT EXCEL"'

Note he shows the internal space character as being preserved.

I´m using Excel 2010, and I don´t want the letter "J" from "June" only the words in upper case

Thanks

 

[Rick Rothstein]

I´m using Excel 2010, and I don´t want the letter "J" from "June" only the words in upper case

Please go back to Message #5 and respond to the question I asked there.

 

[pgc01]

Hi

Will the uppercase words always be together, like in the example, or can there be other words in between, like

"MICROSOFT has the spreadsheet program EXCEL."

In that case you want all those words separated by spaces?