How to Color Cells in Google Sheets Containing Data from a Set

google sheets

I have a Google Spreadsheet having possibly thousands of IP addresses extending from column A1 to column CS2990

And

I have a list of about 100 IP addresses

I need to color all the cells in Green which contain any IP addresses that is in the list of those 100 IP addresses.

The sheet contains IP addresses as 1.2.3.4/32 or 7.8.9.10/28 or something where number after / can change

And the list that I have contains IP addresses in the format of 1.2.3.4

So color the cells having 1.2.3.4, doesn't matter if it was 1.2.3.4/32 or 1.2.3.4/28

Best Answer

you can try this as a custom formula:

=COUNTIF(A1; "*1.2.3.4*")

enter image description here

for 100+ conditions you can do:

=OR(COUNTIF(A1;"*2.2.2.2*");COUNTIF(A1;"*1.2.3.4*");COUNTIF(A1;"*5.5.5.4*"))

or you can set a new sheet with references:

=OR(COUNTIF(A1;"*"&newsheet!A1&"*");COUNTIF(A1; "*"&newsheet!A2&"*"))

or you can use formula builder:

https://docs.google.com/spreadsheets/d/

Related Topic