PDA

View Full Version : Solved: Extracting words in Uppercase



vishwakarma
09-12-2010, 10:57 PM
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
09-13-2010, 12:19 AM
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
09-13-2010, 12:37 AM
Thanks Macropod...:hi:

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


Regards,
Manoj

Bob Phillips
09-13-2010, 01:38 AM
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
09-13-2010, 05:02 AM
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
09-13-2010, 09:37 PM
Yup.. I too tested it and it is returning only the first upper-case word.


Regards,