Forum Discussion
Column validation formula for numbers and special character, no alpha characters
Hello. I'm trying to write a column validation formula for a column that should only accept numbers (0-9) and these special characters ("$", "%", ",", "."). Could someone please advise me on how I could exclude alpha characters from the field? I tried this syntax, but I exceeded the character limit before the alphabet was complete.
=AND(IF(ISERROR(FIND("a",field_7)),TRUE),IF(ISERROR(FIND("b",field_7)),TRUE),IF(ISERROR(FIND("c",field_7)),
Addition to kalpeshvaghela response, here's Microsoft official documentation for pattern matches (RegEx) in Power Apps: IsMatch, Match, and MatchAll functions in Power Apps
Documentation covers special characters, predefined patterns (Digit, Email, Space, etc.) and lot of example to understand it easily.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
4 Replies
Hey, meredithhurston​ ... I know this is 3 years later ... but you could possibly look to using the opposite of this formula (which I cribbed from this stack answer) ... perhaps by wrapping NOT() functions around the IF() statements?
=AND( IF(ISERROR(FIND("~",Title)),TRUE), AND( IF(ISERROR(FIND("@",Title)),TRUE), AND( IF(ISERROR(FIND("!",Title)),TRUE), AND( IF(ISERROR(FIND(",",Title)),TRUE), IF(ISERROR(FIND("&",Title)),TRUE) ) ) ) )I can confirm that the linked positive affirmation has worked on my own tests, but I haven't found a way to make my suggestion, above, work. đŸ˜¥đŸ¤”
According to below thread, SharePoint Column Validation cannot support such a complex RegEx Expression.
https://docs.microsoft.com/en-us/answers/questions/519981/sharepoint-column-validation-regex.html
Here you can use Power Apps for your list form and there you can implement validation using Regular Expression. https://wonderlaura.com/2019/07/23/powerapps-text-input-pattern-validation/
Hope it will helpful to you.
Thank you, kalpeshvaghela. This is helpful. I will try this today. I'm creating the list to use with a powerapps form, so this should be perfect. Thank you for the reference.
Addition to kalpeshvaghela response, here's Microsoft official documentation for pattern matches (RegEx) in Power Apps: IsMatch, Match, and MatchAll functions in Power Apps
Documentation covers special characters, predefined patterns (Digit, Email, Space, etc.) and lot of example to understand it easily.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
