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
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โฆ
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.
You would have to allow the invalid value, then trigger a delete or notification to the user if the validation failed.
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?
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
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |