Only allow specific format on AppSheet text box

[brunotbs]

Hi,

I'm trying to set one text box in my app to only allow text in a specific format. The format should be one letter followed by 1-6 numbers, so x#, x##, x###, x####, x##### and x######.

Is this possible? If so, how do I do it?

Thanks

[AleksiAlkio]

Can the first letter be what ever letter? Can the first number be zero?

[brunotbs]

The first letters will be either C, G or P and the first number will be 1.

Thanks

[AleksiAlkio]

Set the max length first to 7 and then valid_If could be something like..
AND(IN(LEFT([ColumnName],1),{C,G,P}),NUMBER(MID([ColumnName],2,6))>0)

[brunotbs]

Thank you!

I've encountered a couple of issues, though:

- if a user types a number only, they get an error message saying the entry is invalid, which is what I want to happen. However, if they only type a letter, they don't get that message and are allowed to proceed, which I don't want to happen.

- users can type either x-123 or x123 and they won't get an error message. I'm happy for the app to not require the dash, but we can't allow both because we will get duplicate entries from people inserting both x-123 and x123.

Thanks

[AleksiAlkio]

Did you use exactly the same format with Valid_If the formula? I made a test and for me it doesn't allow just G or G- etc.

[brunotbs]

I'm not sure what you mean by same format? I copied the formula you sent above and pasted it on the valid_if box under data validity. The only thing I changed was the ColumnName from your formula as I added the name of the column I wanted this to apply to.

Do you mean column data type? That's currently set to number, which I'm guessing is wrong if I'm including letters and numbers.

Thanks

[AleksiAlkio]

If the formula is the same, then yes you need to change it to text column.

[brunotbs]

Thank you. I changed it to text before and somehow it seemed to make it worst, but it's working now. 

Just one last thing: although the app won't let me type something in between the letter and the numbers, like a dash (ex. x-123), it will allow me to put a space in between them. Is there a way to prevent this?

Thanks.

[AleksiAlkio]

Good to hear!

[brunotbs]

Sorry, just in case you didn't see this:

Thank you. I changed it to text before and somehow it seemed to make it worst, but it's working now. 

Just one last thing: although the app won't let me type something in between the letter and the numbers, like a dash (ex. x-123), it will allow me to put a space in between them. Is there a way to prevent this?

Thanks.

[AleksiAlkio · Accepted solution]

AND(
IN(LEFT([Column],1),{C,G,P}),
NUMBER(MID([Column],2,10))>0,
NOT(CONTAINS([Column]," "))
)

[brunotbs]

Perfect! Thank you 🙂