Text type should not allow the user to type any number or Special Characters

AppSheet AppSheet Q&A
21

Here ya go. It’s ugly, but it’ll do the trick.

Just paste this in the Valid_If expression.

LEN(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER([_THIS]),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z","")
)=0

This will allow upper and lower case letters A-Z only.

4 Likes
22

Been looking for expression for the same problem sir, thank you so much, yours works like a charm unless it’s preventing me to input space (or spaces) for my FULL NAME column.

Any clue sir?

23

Valid_If expression for
A-Z and spaces:

LEN(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER([_THIS]),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z","")," ","")
)=0

Valid_If expression for
A-Z, spaces, hyphen, and underscore.

LEN(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER([_THIS]),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z","")," ",""),"_",""),"-","")
)=0

@Steve , I could not get SUBSTITUTE() to remove apostrophes!

The single quote, or apostrophe can’t be filtered out in the same way these other characters can.
ERROR: Number of opened and closed parentheses does not match.

But this has nothing to do with parentheses. Seems like a bug in the expression assistant’s formula checking.

I tried triple quotes to escape the character but couldn’t get it to work that way either. Any thoughts?

1 Like
24

GreenFlux:

I could not get SUBSTITUTE() to remove apostrophes!

Yup. That due to an inherent limitation in the expression interpreter.

2 Likes
25

Steve:

Yup. That due to an inherent limitation in the expression interpreter.

Well that’s disappointing! I thought maybe it was just a bug in the formula checker that was flagging a valid expression as invalid when using single quotes.

So there’s really no way to remove an apostrophe using SUBSTITUTE()?

26

GreenFlux:

So there’s really no way to remove an apostrophe using SUBSTITUTE()?

Nope.

Unless @Aleksi has a trick?

1 Like
27

Text Validation using List Subtraction and an EnumList of Allowed Values Tips & Tricks ?

Text validation in AppSheet can be a little challenging. [image] There’s no RegEx, and you have to nest 26 SUBSTITUTE()'s just to validate for letters only. And you can nest more SUBSTITUTE()'s for numbers and special characters, but not apostrophes. This sample app uses a different approach; An EnumList table that stores the list of allowable characters, to be reused for validating multiple columns throughout the app-- or for generating lists for buttons and dropdowns. Text Valida…

2 Likes
28

Works well with spaces sir, thank you so much