View Single Post
  #2   Report Post  
Old June 11th 07, 04:04 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier Roger Govier is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default A validation rule on Alpha and Numeric characters

Hi Sar

Use Data ValidationCustom and array entered formula of

{=AND(SUM(CODE(MID(A1,ROW(INDIRECT("1:"&3)),1)))1 94,ISNUMBER(--(RIGHT(A1,4))))}

To Enter or Edit an Array formula, use Control Shift Enter (CSE) not
just Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel
will enter these for you.

On the Error Alert tab type a message for the use showing the type of
entry you expect.

--
Regards

Roger Govier


"Sar*" wrote in message
...
Hey,

I want to create a validation that when i enter a mixed numeric and
alpha
reference that i can prevent changing its format.

For example:

Everytime i enter my "number" FEE1234, that the cell keeps the
formatting
"@@@####" and prevents me using any other format indicating n error
message.

Cheers!
Thanx in advance
Sar*