Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aLPHAnUMERIC validation
Hi How do I validate whether a data entered in a cell in alphanumeric? I dont find any functions available...Is there someway to do using the existing functions?? Thanks Pras |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aLPHAnUMERIC validation
Are you saying you want only letters and numbers in the cell?
wrote in message ups.com... Hi How do I validate whether a data entered in a cell in alphanumeric? I dont find any functions available...Is there someway to do using the existing functions?? Thanks Pras |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aLPHAnUMERIC validation
PJ,
Assume your data is in cell A1. Choose from one of these three. In cell B1 type =ISTEXT(A1), or =ISNUMBER(A1), or =OR(ISTEXT(A1),ISNUMBER(A1)) " wrote: Hi How do I validate whether a data entered in a cell in alphanumeric? I dont find any functions available...Is there someway to do using the existing functions?? Thanks Pras |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aLPHAnUMERIC validation
Yeah I want only letters and numbers in my cell.
ISTEXT returns true even if the cell contains something like w! where ! is not an alphabet. Further the cell might contain both number and alphabet, like "nf45m". In that case none of above would work. Thanks Pras. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aLPHAnUMERIC validation
I believe this works:
To test cell A1, try this formula: B1: =ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz0123456 789"))) That formula is not case-sensitive and returns TRUE if the cell only contains letters and/or numbers. It returns FALSE if blank or if it contains special characters. Does that help? *********** Regards, Ron " wrote: Yeah I want only letters and numbers in my cell. ISTEXT returns true even if the cell contains something like w! where ! is not an alphabet. Further the cell might contain both number and alphabet, like "nf45m". In that case none of above would work. Thanks Pras. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TWO QUESTIONS: Validation & Spacing...PLEASE HELP | Excel Discussion (Misc queries) | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) |