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

AppSheet AppSheet Q&A
1

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

2

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

1 Like
3

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 ,.@#

4

Please go through this thread.

[Data type characters validation?](https://community.appsheet.com/t/data-type-characters-validation/15568/10) Questions

Thank you … i already got similar help from Alekshi (support team) also … my issue resolved now … thank you everyone who responded soo quickly …

1 Like
5

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.

1 Like
6

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.

[Allow multiple sets of parameters in SUBSTITUTE, like SWITCH(), to avoid nesting multiple times](https://community.appsheet.com/t/allow-multiple-sets-of-parameters-in-substitute-like-switch-to-avoid-nesting-multiple-times/24676) Feature Requests

The SUBSTITUTE() function takes 3 arguments: SUBSTITUTE( template , old-text , new-text ) And when you want to substitute multiple values in the template, you have to nest multiple SUBSTITUTE()'s together: 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(SUBST…

[Regular Expressions, Please](https://community.appsheet.com/t/regular-expressions-please/16768) Feature Requests

Regular expressions are my favourite. They’re amazing for processing data of all kinds, especially in the context of data input by many people because it recognises patterns. They’re also so universal, which makes them great for AppSheet, which integrates data from outside sources. My Google Sheets are full of array formulas that use them. Extract and Match are my favourites, but Replace makes the occasional appearance as well. For example, I’m trying to extract the time digits from a time stam…

2 Likes
7

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.

3X_b_8_b88ee1ba6c5904d4e2619c1283a4f0a000c9329c.png
3X_b_8_b88ee1ba6c5904d4e2619c1283a4f0a000c9329c.png827×368 25.8 KB

1 Like
8

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.

1 Like
9

tsuji_koichi:

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.

And include them in the Valid_if expression?

10

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

11

I am getting a error

The expression is valid but its result type ‘Number’ is not one of the expected types: Yes/No, List

12

How did you get to this error? Otherwise we wont be able to put comment…

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

Clean and neat.

1 Like
15

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.

3 Likes
16

Thanks for your wise input @Steve, much appreciated.

2 Likes
17

Which expression i need to use ??

18

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

1 Like
19

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.

20

Which expression is giving the error?