IF statement conditions -- others ??

[Curt1]

The IF statement appears to have the usual “conditions” { = , !=, <,<=, >, >= etc } the standard filters have additional conditions ( contains, Does not contain, Begins with …} can any of these other (filter) conditions be incorporated into the IF sstatement ?

To clarify a bit . I’m trying to write macros (Using the BASIC programming language) to Libreoffice Calc spreadsheets that could benefit from some of the “filtering” conditions of the 'Data ->More Filters → Standard Filter
“conditions” Was wondering if those could be used in the BASIC programming language IF statement.

[mikekaganski]

Given that you mixed calc and basic tags in the question, I am unsure which was actually meant…

[anon73440385]

see REGEX() function, which allows to implement additional conditions in your formulas.

[anon73440385]

I’m trying to write macros

Poor questions → poor answers/comments

[Humilulo]

Yes, but you need to use a little creativity. For instance, to see if text begins with “abc”, we can use:

=IF(LEFT(A1,3)=“abc”,“Y”, “N”)

You can use the RIGHT() function instead of the LEFT() function to see if it ‘ends with’ some text value.
Both of these are case sensitive. If you do not want case sensitivity, then this works:

=IF(LOWER(LEFT(A1,3))=“abc”,“Y”, “N”)

For ‘contains’ functionality, you can use:

=IF(ISERROR(SEARCH(“bcd”, A1)),“N”,“Y”)

(Note that the SEARCH() function returns a number if the text is found and ERROR if it is not found, so unfortunately we need to wrap SEARCH() inside an IsError() function as shown. SEARCH() is not case sensitive. Use FIND() instead if you do want case sensitivity (if you want “abc” and “ABC” to be treated differently).

These are the tricks i have used for years for spreadsheet formulas, and altho they are a bit quirky, they work for other spreadsheet programs too (OpenOffice, Excel, Google’s Sheets, etc.). Unfortunately, these methods suffer that you need to count the letters correctly in the LEFT() and RIGHT() formulas, and using the wrong length (3 in the above examples) can make your formula totally fail, like if 2 was used above instead.

But these formulas are very old, my guess is 3 decades old. And unfortunately, they obviously were not designed with the most user-friendliness in mind. Another option is to write your own macro in a real programming language and call a function in the code you wrote in a real programming language. I had to write my own macro once and it wasn’t too difficult (for an experienced programmer). But it is worth knowing that macros are an option too. So for instance, a person could write their own ‘StartsWith()’ function and use that in formulas in their spreadsheets but then extra care needs to be taken for portability like if you share that spreadsheet with someone else, one needs to make sure the macro is saved to the workbook too. And i only imagine that LibreOffice supports a few extra programming languages that other spreadsheet apps may not, so macros probably have their own pitfalls to be aware of too. Using the above demonstrated formulas are the most portable options across all spreadsheet software to my knowledge.