Data Validity - (Valid If) - Syntax ok but not working?

[Gxstavo]

Hello there, how you're doing?

In the past week I attempted do build an app to help my colleagues productivity, while making the use of paper obsolete.

To mitigate future errors or simple misinputs, I tried creating a validation formula, which would only allow the Row to be filled in the following format "abc1234567", the first 3 digits would be a text and the other 7 would be numbers.

Here's the formula I used:

AND(
LEN([_THIS]) = 10,
AND(TEXT(MID([_THIS],1,1))= "0", MID([_THIS],1,1) <> "0"),
AND(TEXT(MID([_THIS],2,1))= "0", MID([_THIS],2,1) <> "0"),
AND(TEXT(MID([_THIS],3,1))= "0", MID([_THIS],3,1) <> "0"),
OR(NUMBER(RIGHT([_THIS],7)) > 1, RIGHT([_THIS],7) = "0000000")
)

I tried messing around with the formula, but nothing worked, all I get is this:

Is there another way in which I can apply this format restriction?
I thought there would be a easier way to do this 😞

[SkrOYC]

---

@Gxstavo wrote:

AND(TEXT(MID([_THIS],1,1))= "0", MID([_THIS],1,1) <> "0"),

---

You want it to be "0" and not "0" at the same time?

[Aurelien · Accepted solution]

What about:

AND(
  NUMBER(RIGHT([_THIS],7))<>0,
  ISBLANK(EXTRACTNUMBERS(LEFT([_THIS],3))-LIST(""))
)

This is not perfect, but that may do the trick 🙂

Note: I didn't test it, I'm note sure about the NUMBER(RIGHT(...)) output.

For reference:

EXTRACTNUMBERS() - AppSheet Help

NUMBER() - AppSheet Help

[Gxstavo]

I thought of a better way, anyhow, thanks @Aurelien and @SkrOYC !!

Have a good day!