Re: Data Validation - Again!

Dave Peterson wrote:

Just to add to Martin's response:

The =upper() function isn't required. If you're doing a simple
text comparison, excel will return true for:

=UPPER(A1)="x"

If you really only want uppercase X's, you could use =exact():

=OR(EXACT(A1,"X"),AND(ISNUMBER(A1),A1>=DATE(2000,1,1)))

Hi Dave

I was falling back on my AutoLisp programming knowledge when I wrote the
formula and I wanted to make sure that both an uppercase 'X' and a lowercase
'x' would be accepted.

In AutoLisp the comparison is case sensitive, so it is necessary to convert
the text to a known case and compare like for like:

i.e.

(= "x" "X") would return nil (False), whilst

(= (strcase "x") "X") would return T (True)

It didn't occur to me that Excel might not need the text case conversion.

Thanks for the information.
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-


.

Relevant Pages

  • Re: UDF
    ... Close Excel ... In the empty sheet use the function with upper case ... to uppercase indicates a mis-speling. ... Used the function EFF one time in a worksheet. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Uppercase trick not working
    ... I went back and copied the upper/lower doc into Word. ... and it worked -- copied all upper. ... >> Word, make it all Uppercase, then copy and paste in back into Excel. ...
    (microsoft.public.mac.office.excel)
  • All upper
    ... Let's say you have many rows and columns in upper and lower case. ... Does excel ... have quick way to select the whole worksheet and make everything uppercase? ...
    (microsoft.public.mac.office.excel)
  • Re: Uppercase
    ... but have any idea why Excel does not offer this ... He mentioned fonts that are only uppercase. ... have upper and lower. ...
    (microsoft.public.mac.office.excel)
  • Re: UDF
    ... I've found that I can use the Sheet on an addin to call my functions ... in upper case and thus force conversion of entry to upper case. ... How can I best achieve this for an existing Addin? ... Excel and Word Function Lists available free to good homes. ...
    (microsoft.public.excel.worksheet.functions)