Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to valid the values? | Excel Worksheet Functions | |||
Formula to check duplicated values | Excel Discussion (Misc queries) | |||
How to check valid Date value? | Excel Worksheet Functions | |||
Formula to Check Values between to variables | Excel Worksheet Functions | |||
How do I check for values in a list in an if statement? | Excel Discussion (Misc queries) |