Haha, you know the drill by now. First, I followed the outlined solution but then decided to give it another spin this time:
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=. or not(translate(., 'abcdfghijklmnopqrstuvwxyzABCDFGHIJKLMNOPQRSTUVWXYZ', '')) or string-length(.)!=1]"),"")=""
The general structure stays the same: The IFERROR
-function is applying FILTERXML
to convert the cell(s) into queryable XML nodes, then using XPath 1.0 functions to extract/transform content that is not/allowed, and finally, compare it to ""
to get a boolean result (phew).
- No duplicate values: selection non-unique values
preceding::*=.
- filter every alphanumeric value expect
0-9
e
E
: not(translate(., 'alphaNum!09eE', ''))
If you need to disallow other characters add them to the list.
- only allow individual elements of length 1:
string-length(.)!=1
- allowing
,
as a separator
- if you only want to allow e regardless of case
LOWER()
the cell content first
Here is a variation that is closer to the idea of the original formula; it does not work with a list of filtered characters, however, it's even longer:
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(LOWER(A1),",","</s><s>")&"</s></t>","//s[preceding::*=. or not(contains(.,'e') or number(.)<10 or number(.)>-1) or string-length(.)!=1]"),"")=""