Reputation: 69
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
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>"
for1: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 orTRUE
from the comparison. If>0
i.e. at least one match is found, returnsTRUE
, otherwiseFALSE
.
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
thenTRUE
elseFALSE
.
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
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.
To check for 05, make the obvious substitution in the formula.
EDIT#1:
To test a single cell, use:
=ISNUMBER(SEARCH(":" & "5" & ":",":" & A2 & ":"))
Upvotes: 1