Formula to check against a list of valid values
 

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?

Formula to check against a list of valid values
 

One way:

=AND(LEN(A1)=1,OR(CODE(A1)=MEDIAN(CODE(A1),97,122) ,CODE(A1)=MEDIAN(CODE(A1),65,90)))


--
Biff
Microsoft Excel MVP


"Barb W" <Barb wrote in message
...
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?

Formula to check against a list of valid values
 

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.



"T. Valko" wrote:

One way:

=AND(LEN(A1)=1,OR(CODE(A1)=MEDIAN(CODE(A1),97,122) ,CODE(A1)=MEDIAN(CODE(A1),65,90)))


--
Biff
Microsoft Excel MVP


"Barb W" <Barb wrote in message
...
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?

Formula to check against a list of valid values
 

Try this:

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



--
Biff
Microsoft Excel MVP


"Barb W" wrote in message
...
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.



"T. Valko" wrote:

One way:

=AND(LEN(A1)=1,OR(CODE(A1)=MEDIAN(CODE(A1),97,122) ,CODE(A1)=MEDIAN(CODE(A1),65,90)))


--
Biff
Microsoft Excel MVP


"Barb W" <Barb wrote in message
...
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?