How to search a value as number instead of individual digit in a string?

secarica · February 4, 2023, 3:22pm

Suppose I have a cell with whatever date and another cell with a few numbers defined manually. I want to check if some of those numbers matches the day that happen to be at one or the other moment.

The following formula works, but interprets only each individual digit and I don’t know how to either improve the formula and/or to write the string in a proper format so to interpret numbers:
=ISNUMBER(SEARCH(DAY(A1);A2))

For example, if A1 contains some DATE(YYYY;MM;DD) and A2 contains numbers 2 23 (or 2,23 or 2;23), then the formula gives TRUE when the day happens to be either 2 or 3 or 23.

What should I modify so the match be restricted to numbers instead of individual digits?

Zizi64 · February 4, 2023, 3:35pm

Please upload your ODF type sample file here.

secarica · February 4, 2023, 3:56pm

Here:
https://www.secarica.ro/test/test_lo_numbers_to_match_date.ods

(in my above post I state wrongly A0 and A1, where in fact it is A1 and A2 – I edited my post accordingly)

Wanderer · February 4, 2023, 5:25pm

You could include the separator in the match. For a “;”

=ISNUMBER(SEARCH(DAY(A1)&";";A2&";"))

But this is not what you asked for. It solves the problem of found sub-strings, but the formula “knows” nothing about numbers.
[ Not tested ]

Lupp · February 4, 2023, 6:01pm

What about =FIND(TEXT(DAY(A$1);"00");A2)?

Wanderer · February 4, 2023, 6:15pm

@Lupp
You are right, as my suggestion would fail on 3; 12; but actually @secarica would need then 02;23 in the searched cell for the example he gave.
.
Problem starts with having more than one value in a cell - even as we could SUBSTITUTE the “;” with “;0” here as a dirty hack…

erAck · February 4, 2023, 8:07pm

If you used | vertical line (pipe symbol) as a separator like in 2|23 then the string could be used as part of a regular expression to search for ^2|23$ i.e. the day number string must match exactly one of those numbers.

=REGEX(DAY(A1);"^"&A2&"$")

If you need a boolean result like in the document you attached then use

=IFNA(REGEX(DAY(A1);"^"&A2&"$");"")<>""

secarica · February 4, 2023, 10:51pm

erAck:

If you used | vertical line (pipe symbol) as a separator like in 2|23 then the string could be used as part of a regular expression to search for ^2|23$ i.e. the day number string must match exactly one of those numbers.
=REGEX(DAY(A1);"^"&A2&"$")

Well, yes, this is a doable approach, as the right syntax for the matching string condition was not previously specified. Thank you.