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)