How to put text validation for a special character on a particulate cell in excel?

Question

In a particular cell in excel, I have to put validation so a user can not input text values with special charter like "-", ",", "|", "/", ... in between except "_" (underscore).

I have written a custom formula for this and it is working but it has a limitation, isn't resolve my problem completely.

Here is a formula:

=ISNUMBER(FIND("_",A1))

so when a user enters text with some other character like "," or "-" in between text values, it will throw a validation error.

But if a user enters only text without any special character then it also throws an error and user not able to enter text.

if the user enters only text then it allows the text but if the user enters text with special character then it allows only "_" special character.

example:

allowed: "StackOverflow", "Stack_Overflow"
not allowed: "Stack-Overflow", "Stack, Overflow" or any other special character.

So which characters should be allowed? Lower case letters `[a-z]`, upper case letters `[A-Z]`, numbers `[0-9]` and unsderscore `_`? Anything else? What about special non-english characters `öüaßèéñ…` and more? • Please be more precise. • Actually I see no chance to do this with a formula. You will need to code a UDF (user defined function) for that (I recommend to use RegEx). — Pᴇʜ, Jul 04 '19 at 12:00

Pᴇʜ · Answer 1 · 2019-07-04T13:58:52.533

For a more complex test that will only allow these characters [A-Za-z0-9_] use Regular Expressions.

This pattern ^[A-Za-z0-9_]+$ will only allow …

[A-Z] capital letters
[a-z] lower case letters
[0-9] numbers
[_] underscores

… where any of them occur at least once or more. Any other character is not allowed. See and test at: https://regex101.com/r/DnQrAq/1

Option Explicit

Public Function SpecialValidate(ByVal InputValue As String) As Boolean
    Dim RegEx As Object
    Set RegEx = CreateObject("vbscript.regexp")

    Const RegExPattern As String = "^[A-Za-z0-9_]+$"

    With RegEx
        .Pattern = RegExPattern
        .Global = True
        .MultiLine = True

        If .test(InputValue) Then
            SpecialValidate = True
        End If
    End With
End Function

You can then easily use this as formula =SpecialValidate(A2) to validate any cell value:

^{Sorry for the German screenshot: WAHR = TRUE and FALSCH = FALSE}

Better solution Peh Thanks. – Sandeep Bhatt Jul 04 '19 at 13:28 — Sandeep Bhatt, Jul 04 '19 at 13:28

score 1 · Accepted Answer · answered Jul 04 '19 at 12:11

This assumes that a single character in the excluded group is also not valid:

=SUM(ISNUMBER(FIND("-",A1)),ISNUMBER(FIND(",",A1)),ISNUMBER(FIND("|",A1)),ISNUMBER(FIND("/",A1)))=0

Since array constants cannot be included in a validation formula, you can list each excluded character separately.

With a complete list of what should be included or excluded, a more compact formula might be possible.

score 0 · Answer 3 · answered Jul 04 '19 at 12:57

0

You could use:

=IF(IF(LEN(C1)-LEN(SUBSTITUTE(C1,"-",""))>0,1,0)+IF(LEN(C1)-LEN(SUBSTITUTE(C1,",",""))>0,1,0)+IF(LEN(C1)-LEN(SUBSTITUTE(C1,"/",""))>0,1,0)+IF(LEN(C1)-LEN(SUBSTITUTE(C1,"|",""))>0,1,0)>0,"Invalid Character","Correct")

Results:

answered Jul 04 '19 at 12:57

Error 1004

7,877
3
23
46

How to put text validation for a special character on a particulate cell in excel?

3 Answers3