Analyzing Text for Special Characters in a Conditional Formula

Danielle Arteaga

I have two sheets. Sheet 1 (Character Table) has a list of non-alphanumeric text symbols (e.g., !, @, #, $, etc.) and alphanumeric characters (A-Z, a-z and 0-9) alongside their ASCII values. This table distinguishes the non-alphanumeric values from the alphanumeric ones by classifying their types as "Special" or "Alphanumeric."

I have a second table, which contains values that will be entered by users via a form. The values will eventually become user IDs, so they must be AT LEAST 3 characters but NOT MORE THAN 8 characters and CANNOT contains any "Special" character type.

Right now, I have a painfully complex IF statement that looks like this:

=IF(OR(OR(CONTAINS("!", [Choice 2]@row), CONTAINS("@", [Choice 2]@row), CONTAINS("#", [Choice 2]@row), CONTAINS("$", [Choice 2]@row), CONTAINS("%", [Choice 2]@row), CONTAINS("^", [Choice 2]@row), CONTAINS("&", [Choice 2]@row), CONTAINS("*", [Choice 2]@row), CONTAINS("+", [Choice 2]@row), CONTAINS("-", [Choice 2]@row), CONTAINS("_", [Choice 2]@row), CONTAINS("~", [Choice 2]@row)), OR(LEN([Choice 2]@row) < 3, LEN([Choice 2]@row) > 8)), 1, 0)

It works, but it's missing a bunch of special characters, because I can't properly write a formula that recognizes those as text instead of the function they perform. (For example, the ditto mark and the equal sign)

So, I was hoping to be able to write a formula that would reference the Characters table to determine whether the value entered in Choice 2 contains any character for the type Special on the Character Table as well as whether it is the required length.

I feel like it might be some combination of JOIN/COLLECT/CONTAINS, but I can't figure it out. Might be making this overly complex ...?

Paul Newcome

You would need to parse out each digit into its own column. Since you are restricting to a maximum of 8 characters, we can make this relatively straight forward.

=MID([Choice 2]@row, 1, 1)

The above would go in the first column. then you would put the same thing in the second column and update that first 1 to a 2. Then a 3 for the third column, so on and so forth.

Then you would limit your Character Reference Table to only be those characters that are considered invalid.

Finally the formula to flag any row that has a special character would be:

=IF(COUNTIFS(First@row:Eighth@row, HAS({Character Reference Table Character Column}, @cell)) > 0, 1)

Paul Newcome

You would need to parse out each digit into its own column. Since you are restricting to a maximum of 8 characters, we can make this relatively straight forward.

=MID([Choice 2]@row, 1, 1)

The above would go in the first column. then you would put the same thing in the second column and update that first 1 to a 2. Then a 3 for the third column, so on and so forth.

Then you would limit your Character Reference Table to only be those characters that are considered invalid.

Finally the formula to flag any row that has a special character would be:

=IF(COUNTIFS(First@row:Eighth@row, HAS({Character Reference Table Character Column}, @cell)) > 0, 1)

Danielle Arteaga

@Paul Newcome - amazing. Works beautifully, with one very small exception. Entries must be at least 3 characters but not more than 8. Notice my last row:

It has only 2 characters, and neither of them are the invalid ones. Also, need to make sure any entries that exceed 8 characters (but don't contain invalid characters) get flagged. So, I modified the validation formula slightly for those additional conditions:

=IF(LEN([Proposed ID]@row) < 3, 1, IF(LEN([Proposed ID]@row) > 8, 1, IF(COUNTIFS([Char 1]@row:[Char 8]@row, HAS({Special Characters Only Char List}, @cell)) > 0, 1)))

One of these days, I really, really owe you a drink/coffee. Thank you - AGAIN! 😀

Paul Newcome

I actually started off with that but then meant to update it so that it was broken out in 3 separate columns incase you wanted to send different types of automated Update Requests depending on what the issue was. I kept having to stop to take care of other things and by the time I finished with my comment I had forgotten about those other two parts. Maybe a cup of coffee isn't such a bad idea. 🤣

Danielle Arteaga