Try this:
Excel 2010
| A | B |
|---|
| | |
| OVERTEACHING WIZARDRY | OVERTEACHING,WIZARDRY | |
| Undogmatic DOWINESS Matriarchal merenguing extermination Reinterrupt | DOWINESS | |
| dowiness PYCNIDIUM Stung Sthenic | PYCNIDIUM | |
| PACKTHREADED LATINITY | PACKTHREADED,LATINITY | |
| subvirile LUNCHER THAMYRIS Recolor purger | LUNCHER,THAMYRIS | |
| Semiacid merenguing Jahwistic REINTERRUPT SERMONIZED | REINTERRUPT,SERMONIZED | |
| jahwistic OVERTEACHING Overteaching NONREGENERATIVE Mowra | OVERTEACHING,NONREGENERATIVE | |
| alleluiatic SERMONIZED SUBEMARGINATE | SERMONIZED,SUBEMARGINATE | |
| extermination Overrealistic undogmatic MYATONIA | MYATONIA | |
| Descension Loan Reobtain | | |
| luncher Dues Nonadventitious jahwistic overrealistic LUNCHER | LUNCHER | |
| recolor Visaed CORNHUSKING REPEOPLE Degradingly | CORNHUSKING,REPEOPLE | |
<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Raw Data[/TD]
[TD="bgcolor: #D9D9D9"]Output[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=getupper(
A2," ",2,",")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
with this macro code
Code:
Public Function GetUpper(ByVal TextIn As String, ByVal Delim As String, ByVal NumToReturn As Integer, ByVal OPDelim As String) As String
Dim IsUpper()
Dim TopLim As Integer
Dim Uppers As Boolean
Dim Cntr As Integer
Dim Cntr2 As Integer
Dim InChar As String * 1
Dim UppersFound As Integer
Dim OutputStr As String
WordArray = Split(Trim(TextIn), Delim)
OutputStr = ""
TopLim = UBound(WordArray)
ReDim IsUpper(TopLim)
If NumToReturn < 1 Or NumToReturn > TopLim + 1 Then
GetUpper = CVErr(xlErrNum)
Exit Function
End If
For Cntr = 0 To TopLim
For Cntr2 = 1 To Len(WordArray(Cntr))
InChar = Mid(WordArray(Cntr), Cntr2, 1)
Uppers = True
If (InChar Like "[A-Z]") Then
Else
Uppers = False
Exit For
End If
Next Cntr2
IsUpper(Cntr) = Uppers
If Uppers = True Then
UppersFound = UppersFound + 1
If UppersFound = 1 Then
OutputStr = WordArray(Cntr)
Else
If UppersFound <= NumToReturn Then OutputStr = OutputStr & OPDelim & WordArray(Cntr)
End If
End If
Next Cntr
GetUpper = OutputStr
End Function
The structure of the User Defined Formula: GetUpper() is as follows:
GetUpper( Var1, Var2, Var3, Var4)
Where
Var1 = the cell containing the string with various words, some upper case, some lower case - the source data
Var2 = the delimiter of the words. Are they separated by spaces? e.g. "The quick brown fox."
Then use " "
if delimited by commas e.g. "The,Quick,Brown,Fox"
then use ","
Var3 = The number of upper case words you want returned
If you put 1 here, you'll get the first one.
If you put 2, you'll get the first two, and so on.
This value must be between 1 and the max number of words in the source string
Var4 = How you want the outputted uppercase words separated (if more than one is returned)
If you want them separated by just commas then put ","
If just spaces: " "
if comma and space ", "
and so on.
example:
=GetUpper(A1, " ",2,",")
Hope this helps... and makes sense?
