List validation settings: Odd behavior with And()

[JohnBloggs20901055]

Hi there,

I have the following formula:

=AND(OR(NOT(ISERROR(SEARCH("??,????";[Column1])=1));LEN([Column1])=0);OR(NOT(ISERROR(SEARCH("??,????";[Column2])=1));LEN([Column2])=0);OR(NOT(ISERROR(SEARCH("??,????";[Column3])=1));LEN([Column3])=0);OR(NOT(ISERROR(SEARCH("??,????";[Column4])=1));LEN([Column4])=0);OR(NOT(ISERROR(SEARCH("??,????";[Column5])=1));LEN([Column5])=0))

It works in excel, but in validation settings, it always produces true.

What is wrong here

[#2]

@JohnBloggs20901055 Can you provide the data you are using for testing for all these columns?

It will help us to troubleshoot issue with your formula.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

@ganeshsanap I am unsure what you mean. So I'll answer as best as I can.

Each column has the same format. They are single line text fields and you can write freely as you like. The intent is to have week number followed by a comma and then followed by the year (not my choice, just the data format that was needed).

So for example you would write 06,2023. But you could also leave it empty.

The formula works if I remove the AND() and solely focus on one column i.e. column1. Like so: OR(NOT(ISERROR(SEARCH("??,????";[Column1])=1));LEN([Column1])=0). If I write something that is not in the correct format, then I get my error response.