Solved: Extracting words in Uppercase

[vishwakarma]

Hello guys,

is there any way by which we can extract only the upper case words from a sentence?

For E.g :- 1. AMA - Cincinnati
2. AMA - Minnesota
3. SHRM New Jersey Metro EMA Chapter
4. FPRA Central West Coast Chapter
5. NCMA-LA South Bay Chapter


.... I want to extract all the words in Upper case in a seperate cell.


Thanks,
Manoj

[macropod]

Hi Manoj,

You could add a UDF to your workbook, coded as:

Function UpperWords(str As Variant) As String
Dim i As Integer, sTemp As String, StrTmp As String
For i = 0 To UBound(Split(str, " "))
  StrTmp = Split(str, " ")(i)
  If UCase(StrTmp) = StrTmp Then sTemp = sTemp & " " & StrTmp
Next i
UpperWords = Trim(sTemp)
End Function

You could then call this UDF like any other formula. For example:
=UPPERWORDS(A1)

[vishwakarma]

Thanks Macropod...

This is what I wanted... Thanks a lot...


Regards,
Manoj

[Bob Phillips]

You could also try this array formula

=MID(A2,4,MATCH(1,(CODE(MID(A2,ROW(INDIRECT("4:"&LEN(A2))),1))<65)*1,0)-1)

[macropod]

Hi xld,

In my testing, your formula returns only the first upper-case word.

A potential flaw in my code is that it returns numbers also, but that doesn't seem to bother the OP.

[vishwakarma]

Yup.. I too tested it and it is returning only the first upper-case word.


Regards,