How to nest IFS with SEARCH

English
#1

Hey there, I wasn’t having much luck searching for nested IFS with a SEARCH parameter so I hope this is possible.

I am currently using the following formula to search a cell description and then fill accordingly. I would like to use the formula to check for multiple conditions but haven’t been able to determine the proper syntax for multiples.

=IF(ISNUMBER(SEARCH(“toddler”,K7301)),“Toddlers’”,"")

I would like to be able to search for multiple terms.

Bonus points if there is a preferred way to search terms that are parts of others. For example, if I wanted a separate result for men and women. I am guessing ordering of the terms could solve this, but thought there may be a built in way as well.

#2

You probably want something completely different, but it’s hard to tell without sample data and an exact description of what you actually want to achieve. Uploading/attaching a (redacted) sample document might help.

#4

If there is an issue with a formula, first of all tell what’s actually happening if you try to use it. Just telling you didn’t find a solution isn’t of use for somebody trying to help you.

Well, “terns that are part of others” isn’t a clear descriprtion, but you seem to mean substrings. If you better use SEARCH() or FIND() do locate a substring or to decide if it is present at all, depends on details.

Anyway, in your formula supposed string constants are given with curly quotation marks, and this will never work.

#5

It seems the typographic quotation marks are automatically displayed by Discourse in flow text if one does not mark code as such, i.e. by embedding it in ` backticks or using a block surrounded by ``` triple backticks.

#7

The formula I attached works. However, it only searches for one term and then uses that information to populate the cell it resides in with the specific response.

So, for example I may have a column that has a variety of terms in it. For simplicity, let’s use colours.

So, we could have red, green, or blue.

I know how to use the formula attached to populate something based on any single colour listing. So, I can modify the formula attached to print “Red is nice” if the cell contains the word red. However, I would like a way to fill out all of my options in a single formula so that if it is any of those colours, it will print an appropriate response.

For example:
If red, print “Red is nice”
If green, print “Green is gross”
If blue, print “Blue is boring”

I hope that makes things clear

#9

And what result should your formula return if, for example, the cell contains the text “red fox on a green meadow looks at the blue sky”? Just “Red is nice” (because Red appears first in this phrase)? All three prepared phrases separated by commas (because all three words are present)?

#10

And what if “bred in town” …

#11

In my case you wouldn’t have different colours in the same cell. So, no red and blue, etc. However you may have something like blue or bluegreen so I am guessing that the ordering of the formula could address that issue.

#12

All this was already discussed and/or exemplified in the attachment to my answer below.
I will now attach there an enhanced example by editing. It should be final as far as I am concerned.

#3

Instead of nested IF it may be much more effective to check for regular expressions like

=SEARCH("\b(men|women)\b", K7301)

There are informations on regular expressions in the help-files for LibreOffice and also at the source of the library: Regular Expressions - ICU Documentation

Please note: You have to allow regular expressions inside formulas to work with this feature.

#6

Rather use the REGEX() function to match regular expressions than having to enable regular expressions for a whole set of functions with lurking surprises…
REGEX() is available since LO 6.2 already. See help.

#8

To make results depend on (being conditional with respect to) the finding, you will best use an index which you can find with the help of MATCH(). You then can use INDEX() or CHOOSE() for the next step. The handling of multiple findings may be complicated.

For a little demo see the attached sheet.
disask68228findingsInTexts_1.ods (14.7 KB)

Wanting to avoid a very long post to the thread, I included the needed remarks, examples, suggestions… with the attached example document.

I now attach the above (comment on the question) announced enhanced (and final) version.
disask68228findingsInTexts_FINAL.ods (17.0 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.