I infer that you wish to 'filter' all rows with containing one or more words in all capital letters. If so, here is one way using a VBA event handler. The following example will alternately show/hide all rows meeting the criteria simply by double clicking any cell in the worksheet.

Copy this event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, j, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, "A").EntireRow.Hidden = True Then

Cells.EntireRow.Hidden = False

Target.Offset(1).Select

Exit Sub

End If

Next

On Error Resume Next

For i = 1 To LastRow

For j = 1 To Len(Cells(i, "A"))

If Asc(Mid(Cells(i, "A"), j, 1)) > 64 And Asc(Mid(Cells(i, "A"), j, 1)) < 91 Then

ctr = ctr + 1

ElseIf Asc(Mid(Cells(i, "A"), j, 1)) = 32 Then

spCtr = spCtr + 1

End If

Next j

If ctr - spCtr = 1 Then

Cells(i, "A").EntireRow.Hidden = True

End If

ctr = 0

spCtr = 0

Next i

Target.Offset(1).Select

End Sub

Select the worksheet containing your product names and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (Red button - top right).

Assume the following data in A1:A10:

SIERRA

the MAX

Watermelon

apple pie

BLUE BAYOU

Green DAY

The Wild Blue Yonder

TWO Broke Girls

Once Upon A Time In The WILD West

US

Double clicking any cell will filter the following data:

SIERRA

the MAX

BLUE BAYOU

Green DAY

TWO Broke Girls

Once Upon A Time In The WILD West

US

Double click any cell again and all rows will be visible.

Note: this assumes that the first letter of each word is capitalized or all letters are lower case.

Use this formula for cell B2:

=IF(ISNUMBER(FIND(" ",A2,1)),LEFT(A2,FIND(" ",A2,1)-1),A2)

Use this formula for cell C2:

=MID(A2,LEN(B2)+2,LEN(A2))

That'll split off the first word of the product name from the other words. Copy those two cells and paste them two more columns to the right, and it'll split off the next word. Repeat until you've split off all the words. Then way off to the right, use this formula to check whether the first word is in all caps:

=IF(AND(LEN(B2)>0,EXACT( B2,UPPER(B2))),1,0)

Copy that formula to the right, to check each word. It'll return a 1 if they're all caps, and a 0 if they're not. Then have another column where you add all those columns together. Copy it all down to the last row, and sort by that last column.

A2 are product names

B2 =SUBSTITUTE(A2,A2,UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2))))

Copy B2 down to match col A data.