CODEWITHSUNDEEP
excel
user2109946
user2109946

Reputation: 69

check if a cell having multiple numbers contain a specific value

I have a column A with the values 

Col A

A1   1:2:3:3
A2   2:3:4:5
A3   1:04:05

In excel from ColumnA I have to check among the cells A1, A2, A3 if any of the cell contains the number 5 or 05. If true it has to return true. How to get the formula in excel for the above mentioned condition? I tried with Search("5", A1) but it doesn't work?

What formula can be used please guide.

Upvotes: 1

Views: 872

Answers (3)

Terry W
Terry W

Reputation: 3277

If you are using Excel 2013 or later version, you can use the following formula:

=SUMPRODUCT(--(FILTERXML("<b><a>["&SUBSTITUTE(A1,":","]</a><a>[")&"]</a></b>","//a")={"[5]","[05]"}))>0

Drag it down to apply across.

The logic is to use SUBSTITUTE function to convert each string into a valid xml script, such as "<b><a>[1]</a><a>[2]</a><a>[3]</a><a>[3]</a></b>" for 1:2:3:3,

then use FILTERXML function to convert the string into a range of values separated by the : colon sign, such as {"[1]";"[2]";"[3]";"[3]"}. I have added special symbols [ and ] for each value within the range for exact match purpose,

lastly compare the the range with {"[5]","[05]"} and use SUMPRODUCT to find out if there is at least one match or TRUE from the comparison. If >0 i.e. at least one match is found, returns TRUE, otherwise FALSE.

EDIT #2

If you do not have access to FILTERXML function, you can also try the following formula:

=SUMPRODUCT(IFERROR(FIND({"[5]","[05]"},"["&SUBSTITUTE(A1,":","][")&"]"),0))>0

The logic is similar, firstly use SUBSTITUTE function to convert the string 1:2:3:3 into "[1][2][3][3]", then use FIND function to find out if the string contains any of the following {"[5]","[05]"}, and lastly use SUMPRODUCT function to return the result, if >0 then TRUE else FALSE.

Let me know if you have any questions. Here are the strings I have tested:

| Strings            | Result |
|--------------------|--------|
| 1:2:3:3            | FALSE  |
| 2:3:4:5            | TRUE   |
| 1:04:05            | TRUE   |
| 005:50:15:25:55    | FALSE  |
| 505:550:500:05a:O5 | FALSE  |

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

To see if 5 exists in the set of cells, use:

=ISNUMBER(SEARCH(":5:",";" & TEXTJOIN(":",TRUE,A1:A3) & ":"))

This will find 5, but avoid stuff like 55.

enter image description here

To check for 05, make the obvious substitution in the formula.

EDIT#1:

To test a single cell, use:

=ISNUMBER(SEARCH(":" & "5" & ":",":" & A2 & ":"))

enter image description here

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8415

try these:

enter image description here

The "*" is the wildcard...

Or :

enter image description here

Upvotes: 1

Related Questions