LibreOffice Calc – IF(ISNUMBER(SEARCH with 100 conditions

AniruddhaMohod · November 17, 2023, 11:26am

LibreOffice Calc – IF(ISNUMBER(SEARCH with 100 conditions.

I am using Ubuntu 22.04 and LibreOffice 7.5.7.1. Suppose I have data in cell A1 and in B1 I entered formula. But it is allowing me to enter only 18 conditions. I have near-about 100 conditions. What should I do to achieve the desired result.

Formula I used

=IF(ISNUMBER(SEARCH(“121”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“121-A”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“122”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“123”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“124”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“124-A”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“125”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“126”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“127”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“128”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“130”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“131”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“132”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“194”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“195”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“201”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“211(11)”,A1,1)),“Session Triable”,IF(ISNUMBER(SEARCH(“222(1)”,A1,1)),“Session Triable”,""))))))))))))))))))

Extra conditions I want to use in the formula.
225(V), 232, 234, 235(II), 236, 238, 240, 251, 255, 302, 303, 304, 304-B, 305, 306, 307, 308, 311, 313, 314, 315, 316, 326-A, 326-B, 328, 329, 331, 333, 363A(II), 364, 364-A, 366, 366-A & B, 367, 370, 370-A, 371, 372, 373, 376, 376-A, 376-B, 376-C, 376-D, 376-E, 395, 396, 397, 398, 399, 400, 402, 412, 413, 436, 437, 438, 439, 449, 450, 459, 460, 489-A, 489-B, 489-C, 489-D, 353, 332

LeroyG · November 17, 2023, 11:44am

Version: 7.5.8.2
Otherwise, you can use as last argument a reference to another cell with more IFs.

JohnSUN · November 17, 2023, 1:19pm

Well, I don’t see 100 conditions here, I counted only 83 constants, for which the value of cell A1 should be checked. And there is only one condition - does cell A1 contain any of these constants?
Are you sure you want to paste this entire list into one formula? Perhaps it makes sense to collect all these constants somewhere on a sheet in the form of a separate list and use something like this?
=IF(SUMPRODUCT(COUNT(MATCH(".*"&$F$2:$F$84;A1;0)));"Session Triable";"")

In this case, you will be spared the hassle of changing the constants in the formula - just add a few more values to the list and the formula will continue to work.

karolus · November 17, 2023, 12:00pm

Hallo

SEARCH can deal with regular Expressions

Concatenate all the stuff you search for in one Expression like:

IF(ISNUMBER(SEARCH("121\-A|121|122|123|124\-A|124| … ",A1,1))

rule of thumb:

1. put the longer similar Expression before the shorter
2. escape the literal ( ) with prefix backslash  \( \)

even better:

=IF(ISNUMBER(SEARCH(  TEXTJOIN("|";1;  seperate_CellRange_ with_the_values )  ;A1;1)); "Session Triable")

according to @sokol92

=IF(ISERROR(REGEX(A1;TEXTJOIN("|";1;seperate_Cellrange)));"";"Session Triable")

sokol92 · November 17, 2023, 12:36pm

Not all users have the SEARCH function configured to use regular expressions. We can use REGEX.

koyotak · November 17, 2023, 1:47pm

I have tested your last formula with expanded strings (WORD) such as “121999" instead of “121” with the wrong result TRUE but not correctly FALSE because “121” is inherent of such string “121999” or “999121” and so on.
I need a solution outside of the asking user and your good formulas above, which compares only the same string-WORDs between 1 cell and an area.
supplement: question/answer explizid to @karolus cause WebSide is imperfect programed

keme1 · November 17, 2023, 2:09pm

It looks like your needs are different but similar to the original question. The matches not acceptable to you are given as acceptable (it seems) to the original poster. In this situation I guess you should post a new question, but to link to this one for reference.

Also, this issue is marked as solved, which makes it less likely that your supplemental question gets the attention it deserves.

koyotak · November 17, 2023, 5:08pm

so i have it done: