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

I am having a column as Text type should not allow the user to type any number or Special Characters

the field should contain only characters,any suggestions

0 27 2,609
27 REPLIES 27

If there were any special characters in your question it could have helped us understand it better.

I am having a column name and type is TEXT
i need enter only text into the field ,it should not any numbers like 123
special characters like ,.@#

Please go through this thread.

I have faced similar problems in the past. I myself have not found a good compact solution for this yet. And seeing this thread I havenโ€™t asked for one.

As possible workaround, prepare column(s) validity checks.
First check for if or not it incluing the number with expression something like count(EXTRACTNUMBERS([Col1]))

If this number is > 0 then obviously the user is typing some number in the text/longtext fields.

likewise, number of contain expression to check if the special characters are in or not.

Upon failing those test, the user can not save the form at the end.

And include them in the Valid_if expression?

Yes
Writing number of contain expression is a bit of pain though.

I am getting a error

The expression is valid but its result type โ€˜Numberโ€™ is not one of the expected types: Yes/No, List

How did you get to this error? Otherwise we wont be able to put commentโ€ฆ

GreenFlux
Participant V

Thereโ€™s no easy way to do this in AppSheet. You can use SUBSTITUTE() to remove a single value, but you would have to nest 26 expressions together.

You could try using UPPER(), then SUBSTITUTE() x 26 to remove all letters from the original string. Then, if the LENGTH()>0, you will know there are other characters remaining.


Here are a few related feature requests in case youโ€™d like to vote for better text parsing tools.

One other option is using a spreadsheet formula with REGEXMATCH. However, the formula result will not be applied until sync, so the user will not see that they are entering an invalid value.

You would have to allow the invalid value, then trigger a delete or notification to the user if the validation failed.

LeventK
Participant V
AND(
	NOT(
		CONTAINS(
			[ColA],
			{"0" , "1" , "2" , "3" , "4" , "5" , "6" , "7" , "8" , "9"}
		)
	),
	NOT(
		CONTAINS(
			[ColA],
			{"@" , "," , "." , "|" , "!" , "_" , "-" , "<" , ">" , "&" , "#" , "(" , ")" , "[" , "]" , "*"}
		)
	)
)

Clean and neat.

This wonโ€™t work. CONTAINS() converts the list to text, so the first becomes:

CONTAINS([ColA], "0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9")

which is unlikely to ever be true.

Thanks for your wise input @Steve, much appreciated.

Which expression i need to use ??

@Gunasuriya_Ravi
Arenโ€™t you following the posts under this thread? What isnโ€™t clear to you?

LeventK,i was wondering that above expression i have used in valid If section ,its coming error again has LIST has elements of mismatched types.

Which expression is giving the error?

GreenFlux
Participant V

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.

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?

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?

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()?

Nope.

Unless @Aleksi has a trick?

Works well with spaces sir, thank you so much

Top Labels in this Space