Formula to filter data in columns where some strings are in UPPER case

[joy1mat]

Hi,

I'm trying to find a formula to help me filter rows that contain some words in upper case. 70% of the range I'm trying to filter has no data in the cell. Cells with data (single or multiple words) all have a leading Capital eg Proper Text or data which contains multiple words with some ALLCAPS. What I need to filter is only those cells where a word in in ALLCAPS so in the example below I only need to filter A4 and A5 eg Mr WORD or Mr POWERPOINT. My workbook has 10K+ rows of data and the data in UPPER are surnames and no surnames are repeated. I've tried many formulas but none of them work so it would be amazing is someone could help with this.

A1 =
A2 = Mr Excel
A3 =
A4 = Mr WORD
A5 = Mr POWERPOINT
A6 =
A7 = Word

 

[hagia_sofia]

Hello, maybe something like:

=FILTER(A2:A1000,BYROW(A2:A1000,LAMBDA(x,IF(x="",FALSE,OR(EXACT(TEXTSPLIT(x," "),TEXTSPLIT(UPPER(x)," ")))))))

Or if you have REGEX functions:

=FILTER(A2:A1000,REGEXTEST(A2:A1000,"\b[A-Z]+\b"))

 

[joy1mat]

Hello, maybe something like:

=FILTER(A2:A1000,BYROW(A2:A1000,LAMBDA(x,IF(x="",FALSE,OR(EXACT(TEXTSPLIT(x," "),TEXTSPLIT(UPPER(x)," ")))))))

Or if you have REGEX functions:

=FILTER(A2:A1000,REGEXTEST(A2:A1000,"\b[A-Z]+\b"))

Hi, I couldn't get either of these to work. The first returned a value for even the blank cells in the column and where there was a cell with an ALLCAPS name included the output of the formula didn't match the row it was against. I don't have REGEX functions so that returns an #NAME? error. Sorry I'm starting to think what I'm after is complex.

 

[Peter_SSs]

The first returned a value for even the blank cells in the column

Are you sure those cells are actually blank and don't contain, say, a space character?
The first formula from post 2 works for me as shown in column C.
I have suggested a shorter formula in column B.

If you still cannot get something to work, perhaps you could give us (with XL2BB ) some dummy sample data to test with?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

25 03 25.xlsm
	A	B	C
1
2	Mr Excel	Mr WORD	Mr WORD
3		Mr POWERPOINT	Mr POWERPOINT
4	Mr WORD
5	Mr POWERPOINT
6
7	Word
8
9
10
Filter case

Cell Formulas
Range	Formula
B2:B3	B2	=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r)))))
C2:C3	C2	=FILTER(A2:A1000,BYROW(A2:A1000,LAMBDA(x,IF(x="",FALSE,OR(EXACT(TEXTSPLIT(x," "),TEXTSPLIT(UPPER(x)," ")))))))
Dynamic array formulas.

 

[joy1mat]

Are you sure those cells are actually blank and don't contain, say, a space character?
The first formula from post 2 works for me as shown in column C.
I have suggested a shorter formula in column B.

If you still cannot get something to work, perhaps you could give us (with XL2BB ) some dummy sample data to test with?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

25 03 25.xlsm
	A	B	C
1
2	Mr Excel	Mr WORD	Mr WORD
3		Mr POWERPOINT	Mr POWERPOINT
4	Mr WORD
5	Mr POWERPOINT
6
7	Word
8
9
10
Filter case

Cell Formulas
Range	Formula
B2:B3	B2	=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r)))))
C2:C3	C2	=FILTER(A2:A1000,BYROW(A2:A1000,LAMBDA(x,IF(x="",FALSE,OR(EXACT(TEXTSPLIT(x," "),TEXTSPLIT(UPPER(x)," ")))))))
Dynamic array formulas.

Hi, thank you so much for the help you given me so far. I had a look at setting up the XLB22 Add-on though the device I'm working on isn't mine, so I won't be able to do that. I've created a file with dummy names so you can see the type of data I'm trying to filter and posted an image of what it looks like. The real workbook has over 10K rows so is quite large and there are no hidden characters in the cells that are blank eg C5. I've run both the above formulas and can see these work though I won't be able to filter the results. In column D I'm looking for a formula that allows me to filter the results. In this sample I can filter all the True results and ignor the False ones. The output could be Yes/No or anything that lets me filter the rows to just show those where there is a surname in CAPS. I hope this makes sense.

 

[hagia_sofia]

If the inteded outcome is this kind of filter, then you just need to slightly adjust the formula posted by @Peter_SSs above. I.e.

=IF(BYROW(A2:A1000,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r)))),"Yes","No")

or

=IF(COUNT(FIND(TEXTSPLIT(UPPER(A2)," "),A3)),"Yes","No")

 

[Peter_SSs]

Similar to what hagia_sofia has mentioned first above in col D or, if you have the TRIMRANGE function (& TrimRefs notation), then you could use the E2 format that will only spill down as far as required.

25 03 25.xlsm
	C	D	E
1
2	Alias JONES	TRUE	TRUE
3	Alias WHITE	TRUE	TRUE
4	Alias Joanna	FALSE	FALSE
5		FALSE	FALSE
6		FALSE	FALSE
7	Alias Margaret	FALSE	FALSE
8		FALSE	FALSE
9	Alias ADAMS	TRUE	TRUE
10	Alias Sarah	FALSE	FALSE
11		FALSE	FALSE
12	Alias BLACK	TRUE	TRUE
13		FALSE
14		FALSE
15		FALSE
16		FALSE
17		FALSE
Filter case

Cell Formulas
Range	Formula
D2:D1000	D2	=BYROW(C2:C1000,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r))))>0
E2:E12	E2	=BYROW(C2:.C1000,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r))))>0
Dynamic array formulas.

 

[joy1mat]

Similar to what hagia_sofia has mentioned first above in col D or, if you have the TRIMRANGE function (& TrimRefs notation), then you could use the E2 format that will only spill down as far as required.

25 03 25.xlsm
	C	D	E
1
2	Alias JONES	TRUE	TRUE
3	Alias WHITE	TRUE	TRUE
4	Alias Joanna	FALSE	FALSE
5		FALSE	FALSE
6		FALSE	FALSE
7	Alias Margaret	FALSE	FALSE
8		FALSE	FALSE
9	Alias ADAMS	TRUE	TRUE
10	Alias Sarah	FALSE	FALSE
11		FALSE	FALSE
12	Alias BLACK	TRUE	TRUE
13		FALSE
14		FALSE
15		FALSE
16		FALSE
17		FALSE
Filter case

Cell Formulas
Range	Formula
D2:D1000	D2	=BYROW(C2:C1000,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r))))>0
E2:E12	E2	=BYROW(C2:.C1000,LAMBDA(r,COUNT(FIND(TEXTSPLIT(UPPER(r)," "),r))))>0
Dynamic array formulas.

Hi Peter, yes that did work. You're a legend and I appreciate all your help along with the others who contributed.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.