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)))


--
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?

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?

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?