#1

Based on @mikekaganski’s answer at Calc: How to specify formula for custom data validity? - #2 by mikekaganski, I tried to write a validity formula matching the newline in a cell, for example:

1
2

This works as a validity formula:

NOT(ISERROR(SEARCH("2";A2)))

And this works as a spreadsheet formula but not a validity formula.

=NOT(ISERROR(SEARCH(CHAR(10);A2)))

Any idea why it does not work, or if there is a way to make it work?

Apparently newlines are handled at least to some extent, because this works:

NOT(ISERROR(SEARCH(CHAR(10);"1" & CHAR(10) & "2")))

The problem comes from my attempted answer at https://superuser.com/a/1657145/541756.

#2

It looks like a bug.

#3

Okay, I’ll file a bug report after a day or so if no one else here has any further comments.

#4

Conditional formatting works correctly with the specified formula.

#5

I created your formula in the “Manage Names” window and named it VALIDATE, then wrapped it in the “Validity” window with the INDIRECT function… Alas, not working.

INDIRECT(VALIDATE)

Searching with REGEX(<…>;"\n") also failed.

In short, your formula doesn’t work in Excel either.

And this one doesn’t work either (accepts everything except NewLine):

=ISNUMBER(FIND(CHAR(10);A2))

I believe it is slightly better than the one you suggested :blush::

=NOT(ISERROR(SEARCH(CHAR(10);A2)))

#6

btw.
As I stated on superuser.com

Documentation in this link:
https://help.libreoffice.org/7.1/en-US/text/scalc/01/12120100.html

is incomplete as there is no information or any reference to the “formula” documentation site.
Even there is not mention that there is “Non standard” filtering option, i mean the last mentioned option is “Text length” and there is a lack of “Non standard” option.

#7

“Custom” option means “Formula” (missing from the list of documentation).

#8

Documentation … is incomplete

which is an own bug :wink:

… fixed in 7.2: Criteria

#9

Filed as bug 142934: https://bugs.documentfoundation.org/show_bug.cgi?id=142934

Already reported as bug 125595: https://bugs.documentfoundation.org/show_bug.cgi?id=125595#c4