excel - find exact value in a text-CodePudding

So I have the following text:

192.1.2.30,192.1.2.39
192.1.2.32,192.1.2.3

using this formula =COUNTIF(A:A,""&D1&"") this check if the IP address is in the text. Which is the issue. it's a wild card search D1 - D4

192.1.2.30 >> result >> 1 >> CORRECT
192.1.2.39 >> result >> 1 >> CORRECT
192.1.2.3 >> result >> 2 >> **INCORRECT** >> should be 1
192.1.2.32 >> result >> 1 >> CORRECT

192.1.2.3 shows as 2 because 192.1.2.3 is part of 192.1.2.30.

Is there a way exclude the incorrect IP as matching twice?

CodePudding user response：

If your version supports TEXTJOIN() then could try-

=SUM(--(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("</s><s>",TRUE,$A$1:$A$10),",","</s><s>")&"</s></t>","//s")=C1))

CodePudding user response：

=COUNTIF(A:A,"*"&D1&"*") counts the total number of rows where D1 is a substring.

You want to get only a substring with full IP Address, that is you need to differentiate a full IP address.

Your input is separated by commas so we can utilize that. We are sure it's an IP if it is between two commas.

=COUNTIF(A:A,"*,"&D1&",*")

However the first and last IP addresses does not adhere to this. I would just add commas in front and end of input so it becomes consistent for our first and last IP via:

=","&A1&","