# FILTER Function Challenge



## excelbytes (Dec 20, 2022)

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 (Dec 20, 2022)

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 (Dec 20, 2022)

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.
Book1AB10203Green04Red0Sheet1Cell FormulasRangeFormulaB1:B4B1=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 (Dec 20, 2022)

Maybe you missed this


excelbytes said:


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


----------



## RoryA (Dec 20, 2022)

Untested as I only have 2016 here, but something like


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

should work


----------



## jdellasala (Dec 20, 2022)

Fluff said:


> Maybe you missed this


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


----------



## excelbytes (Dec 20, 2022)

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 (Dec 20, 2022)

excelbytes said:


> 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.xlsmAB1ABC MartinABC2ABCABC, Smith3ABC JonesABC Martin, ABC4ABC, Smith5ABC Martin, ABC6FILTERCell FormulasRangeFormulaB1:B3B1=FILTER(A1:A23,ISNUMBER(FIND(", ABC,",", "&A1:A23&",")))Dynamic array formulas.


----------



## excelbytes (Dec 21, 2022)

Thanks, Peter_SSs, that worked!


----------



## Peter_SSs (Dec 21, 2022)

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


----------

