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


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 or or something where number after / can change

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

So color the cells having, doesn't matter if it was or

1 Answer 1


you can try this as a custom formula:

=COUNTIF(A1; "**")

enter image description here

for 100+ conditions you can do:


or you can set a new sheet with references:

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

or you can use formula builder:


  • Thanks, but I have 100 of those IP addresses, so I'd need to do this 100 times. Isn't there a way where I can give all 100 IP addresses at once? Aug 20, 2018 at 1:06
  • @GypsyCosmonaut it's difficult because you are playing with masks ("*abc...*"), but I can think of only such way which I added to the end of my answer
    – user0
    Aug 20, 2018 at 3:00
  • =OR(COUNTIF(A1;"**");COUNTIF(A1;"**");COUNTIF(A1;"**")) This helped and I created a script to generate the formula in this format for 100 IPs and it worked. Thanks Aug 20, 2018 at 13:01
  • 1
    For people who'd like to know what I did, the solution is for linux users, or users using linux tools somehow on Windows with Cygwin. I pasted the list of IP addresses in file separated by newline and named the file ip and ran the following command echo '=OR('$(while read ip; do echo 'COUNTIF(A1;"*'$ip'*");'; done < ip | tr '\n' '%' | sed 's/%//g')')' which gave me the formula Aug 20, 2018 at 13:06
  • 1
    @GypsyCosmonaut nice. non-linux users can use docs.google.com/spreadsheets/d/… to generate custom formula by populating A column
    – user0
    Aug 20, 2018 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.