FILTER Function Challenge

[excelbytes]

I have a list like the attached. I want to use the FILTER function to filter only the rows that are green (there is no fill color on the data, I just shaded them for an example). The ones in green contain ABC only as one of the items in the string. Can this be done?

 

[Fluff]

Can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jdellasala]

Shockingly, this is a VBA problem. The CELL function used to return colors (as best as I can remember), but in the latest 365 (Insider), it doesn't work.

Book1
	A	B
1		0
2		0
3	Green	0
4	Red	0
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=CELL("color",A1)

I'm not aware of any other Excel function that would return a cell's color.
Is there by any chance an underlying Conditional Formatting rule in play?

 

[Fluff]

Maybe you missed this

(there is no fill color on the data, I just shaded them for an example)

 

[RoryA]

Untested as I only have 2016 here, but something like

=FILTER(A1:A23,ISNUMBER(FIND(",ABC,",","&A1:A23&",")))

should work

 

[jdellasala]

Maybe you missed this

Yes I did, but I have a plane to catch right now!

 

[excelbytes]

RoryA,

That worked almost perfectly. It didn't include where ABC was at the end of the string, but I added a criteria for that. Thanks!

 

[Peter_SSs]

I added a criteria for that.

Rather than add a criteria, I think that you could just modify Rory's criteria a little.

22 12 21.xlsm
	A	B
1	ABC Martin	ABC
2	ABC	ABC, Smith
3	ABC Jones	ABC Martin, ABC
4	ABC, Smith
5	ABC Martin, ABC
6
FILTER

Cell Formulas
Range	Formula
B1:B3	B1	=FILTER(A1:A23,ISNUMBER(FIND(", ABC,",", "&A1:A23&",")))
Dynamic array formulas.

 

[excelbytes]

Thanks, Peter_SSs, that worked!

 

[Peter_SSs]

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