1

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

1

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/

  • 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? – GypsyCosmonaut Aug 20 '18 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 '18 at 3:00
  • =OR(COUNTIF(A1;"*2.2.2.2*");COUNTIF(A1;"*1.2.3.4*");COUNTIF(A1;"*5.5.5.4*")) This helped and I created a script to generate the formula in this format for 100 IPs and it worked. Thanks – GypsyCosmonaut Aug 20 '18 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 – GypsyCosmonaut Aug 20 '18 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 '18 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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