Calc validity formula matching a newline

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:


This works as a validity formula:


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


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

It looks like a bug.

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

Conditional formatting works correctly with the specified formula.

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.


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):


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


As I stated on

Documentation in this link:

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.

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

Documentation … is incomplete

which is an own bug :wink:

… fixed in 7.2: Criteria

Filed as bug 142934:

Already reported as bug 125595: