Formula to check against a list of valid values

  • Thread starter Thread starter Barb W
  • Start date Start date
B

Barb W

I am importing data into a spreadsheet. I want to check whether certain
columns contain valid data. So if columns A and D, for instance, need to be
"character", I would want to check that the value in any given field is A-Z
or a-z. Is there a quicker way than using "OR" and typing out the 52
possibilities?
 
One way:

=AND(LEN(A1)=1,OR(CODE(A1)=MEDIAN(CODE(A1),97,122),CODE(A1)=MEDIAN(CODE(A1),65,90)))
 
Okay, so this is an amazing formula! Unfortunately, it doesn't quite work
for me. It appears to depend on only having a single character in the field.
My original post wasn't very clear, but I need to check a column (several
different columns, actually) which could be variable length, to ensure all
the characters are in the valid range.

So, "ABC" or "AbCDefg" would be valid, but "AB^m" would not be.
 
Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))=LEN(A1)
 
Back
Top