excel - find exact value in a text

user_2137570 0
Viewed 47

Excel setup

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 checks 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?

excel
3 Answers

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))

enter image description here

user_55147470

=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&","

user_112188910

Assuming that all search patterns start with 192, there are three main ways of counting the matches of a string exactly:

(1) Countif with wildcards:

=COUNTIF(A$2:A$9,"*"&C2&",*")+COUNTIF(A$2:A$9,"*"&C2)

(2) Find with isnumber and sum (or sumproduct):

=SUM(--ISNUMBER(FIND(C2&",",A$2:A$9&",")))

(3) Substitute with len and sum:

=SUM((LEN(A$2:A$9&",")-LEN(SUBSTITUTE(A$2:A$9&",",C2&",","")))/LEN(C2&","))

The results are as follows:

enter image description here

Countif counts once per row as long as the same IP address can't be repeated in the same row.

Find only counts once per row.

Substitute counts multiple occurrences per row.

If the above assumption doesn't hold (e.g. you want to search for 92.1.2.3 and still demand an exact match), then you would have the following:

(1) Using countif to count the number of lines containing the string, it's necessary to consider separate cases for when the string is on its own, at the start of a line, in the middle of a line, or at the end of a line. Assumes as before that there is no more than one occurrence per line:

=COUNTIF(A$2:A$9,C2)+COUNTIF(A$2:A$9,C2&",*")+COUNTIF(A$2:A$9,"*,"&C2&",*")+COUNTIF(A$2:A$9,"*,"&C2)

(2) Find still counts the number of lines containing at least one occurrence if the string is fully delimited:

=SUM(--ISNUMBER(FIND(","&C2&",",","&A$2:A$9&",")))

(3) I don't think you can count all occurrences including multiple occurrences in the same line with substitute in the general case because it becomes recursive (not without VBA or, if you had Excel 365, a lambda, but in that case you would use Filterxml anyway).

user_38949170
Related