Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 28th 08, 10:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default alpha numeric data validation for excel

=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7

it does get rid of the volatile function calls (I wonder if that matter in
a validation formula)


Well, it leaves the formula vulnerable to row insertions and it accepts more
than 7 characters. It appears that a formula with a volatile function used
as a validation rule doesn't "act" volatile. In a test file I entered the
validation rule using INDIRECT. Closed the file, opened the file, didn't do
anything at all, then closed the file without Excel asking if I wanted to
save changes ( a telltale sign that a volatile function is in use)

We can shorten it by a few more keystrokes while at the same time adding a
length test since a formula entered in a refedit is automatically processed
as an array:

=SUM(--(LEN(A2)=7),--(ABS(CODE(MID(A2,ROW($1:$2),1))-77.5)<=12.5),COUNT(-MID(A2,ROW($3:$7),1)))=8

So, as long as you don't insert new rows (in certain places) that looks
pretty good.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Okay, this is only a little shorter, but it does get rid of the volatile
function calls (I wonder if that matter in a validation formula)...

=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
That allows more than 7 characters:

AB12345xxxxxx
AB1234567890

This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
With the validated cell being A2:

=PRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:2")),1))=65))*PRODUC T(--(CODE
(MID(A2,ROW(INDIRECT("1:2")),1))<=90))*PRODUCT(--ISNUMBER(--MID(A2,ROW
(INDIRECT("3:7")),1)))

A bit complex but ensures that no . or E will be used in the last five
digits.

HTH
Kostis Vezerides

On Nov 28, 7:23 pm, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?







  #12   Report Post  
Old November 28th 08, 11:08 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default alpha numeric data validation for excel

Doh... of course it needed a LEN test (can't believe I missed that).

So then, I guess INDIRECT is indirect only when used directly.<g

Yes, using the automatic array process is definitely better. Of course, I
tested my logic out on the grid directly and used SUMPRODUCT during testing
to avoid hitting the Ctrl+Shift+Enter (just lazy on my part); then, because
my wife wanted to use the computer, I rushed posting my findings without
even thinking about the array processing part of it.

Thanks for catching all the issue I missed.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7


it does get rid of the volatile function calls (I wonder if that matter in
a validation formula)


Well, it leaves the formula vulnerable to row insertions and it accepts
more than 7 characters. It appears that a formula with a volatile function
used as a validation rule doesn't "act" volatile. In a test file I entered
the validation rule using INDIRECT. Closed the file, opened the file,
didn't do anything at all, then closed the file without Excel asking if I
wanted to save changes ( a telltale sign that a volatile function is in
use)

We can shorten it by a few more keystrokes while at the same time adding a
length test since a formula entered in a refedit is automatically
processed as an array:

=SUM(--(LEN(A2)=7),--(ABS(CODE(MID(A2,ROW($1:$2),1))-77.5)<=12.5),COUNT(-MID(A2,ROW($3:$7),1)))=8

So, as long as you don't insert new rows (in certain places) that looks
pretty good.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Okay, this is only a little shorter, but it does get rid of the volatile
function calls (I wonder if that matter in a validation formula)...

=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
That allows more than 7 characters:

AB12345xxxxxx
AB1234567890

This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
With the validated cell being A2:

=PRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:2")),1))=65))*PRODUC T(--(CODE
(MID(A2,ROW(INDIRECT("1:2")),1))<=90))*PRODUCT(--ISNUMBER(--MID(A2,ROW
(INDIRECT("3:7")),1)))

A bit complex but ensures that no . or E will be used in the last five
digits.

HTH
Kostis Vezerides

On Nov 28, 7:23 pm, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?






  #13   Report Post  
Old November 29th 08, 01:32 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,766
Default alpha numeric data validation for excel

Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?


  #14   Report Post  
Old November 29th 08, 01:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default alpha numeric data validation for excel

That formula fails when the number part has a decimal point or an E (or e)
in it. For example, try these values...

AB123.5

AB123e5

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?



  #15   Report Post  
Old November 29th 08, 02:33 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default alpha numeric data validation for excel

"T. Valko" wrote...
....
This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),
--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

....

Or avoiding the volatile INDIRECT function but taking advantage of
ASCII encoding,

=AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))-77.5)<13,COUNT(-MID(x,
{3;4;5;6;7},1))=5)


  #16   Report Post  
Old November 29th 08, 02:45 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,766
Default alpha numeric data validation for excel

Hi,

Thank you for your comments. In the original question, it has been
mentioned that the last 5 characters are numbers - therefore the possibility
of having a . or e is ruled out.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
That formula fails when the number part has a decimal point or an E (or e)
in it. For example, try these values...

AB123.5

AB123e5

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?



  #17   Report Post  
Old November 29th 08, 03:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 5,934
Default alpha numeric data validation for excel

The OP was after a Data/Validation formula that would guarantee a user's
input was of the form letter-letter-number-number-number-number-number...
that is the desired entry format... that doesn't mean the user will *always*
type in such an entry... for example, the user could type in AB123e5 by
mistake (accidentally hitting the 'e' when he/she went for the '4' instead)
or he/she could type in AB123.4 just to be mischievous... I would think the
Data/Validation formula should be able to handle such occurrences.

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Thank you for your comments. In the original question, it has been
mentioned that the last 5 characters are numbers - therefore the
possibility of having a . or e is ruled out.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
That formula fails when the number part has a decimal point or an E (or
e) in it. For example, try these values...

AB123.5

AB123e5

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?



  #18   Report Post  
Old November 29th 08, 03:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default alpha numeric data validation for excel

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),
--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

...

Or avoiding the volatile INDIRECT function but taking advantage of
ASCII encoding,

=AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))-77.5)<13,COUNT(-MID(x,
{3;4;5;6;7},1))=5)


Nice one, Harlan.

I should've realized we could use array constants rather than
ROW(INDIRECT(...)) since it's only a few characters.

About the volatile INDIRECT...

"T. Valko" wrote...
It appears that a formula with a volatile function used
as a validation rule doesn't "act" volatile. In a test file
I entered the validation rule using INDIRECT. Closed
the file, opened the file, didn't do anything at all, then
closed the file without Excel asking if I wanted to save
changes ( a telltale sign that a volatile function is in use)


Any thoughts on that?

--
Biff
Microsoft Excel MVP


  #19   Report Post  
Old November 29th 08, 05:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,766
Default alpha numeric data validation for excel

Hi,

Agreed. Thank you for the clarification.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
The OP was after a Data/Validation formula that would guarantee a user's
input was of the form letter-letter-number-number-number-number-number...
that is the desired entry format... that doesn't mean the user will
*always* type in such an entry... for example, the user could type in
AB123e5 by mistake (accidentally hitting the 'e' when he/she went for the
'4' instead) or he/she could type in AB123.4 just to be mischievous... I
would think the Data/Validation formula should be able to handle such
occurrences.

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Thank you for your comments. In the original question, it has been
mentioned that the last 5 characters are numbers - therefore the
possibility of having a . or e is ruled out.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
That formula fails when the number part has a decimal point or an E (or
e) in it. For example, try these values...

AB123.5

AB123e5

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

Try this formula. While in cell B85, enter this in Data Validation
Custom.

=AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?



  #20   Report Post  
Old November 29th 08, 06:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 1,231
Default alpha numeric data validation for excel

"T. Valko" wrote...
....
About the volatile INDIRECT...

"T. Valko" wrote...
It appears that a formula with a volatile function used
as a validation rule doesn't "act" volatile. In a test file
I entered the validation rule using INDIRECT. Closed
the file, opened the file, didn't do anything at all, then
closed the file without Excel asking if I wanted to save
changes ( a telltale sign that a volatile function is in use)


Any thoughts on that?


Validation rules only apply when you enter something into cells with
validation rules. If you're not entering anything, e.g., when saving
files, then the validation rule wouldn't be evaluated. Key point:
validation rules are OUTSIDE the calculation dependency tree.

Validation permits initial entry, which in turn triggers minimal
recalc, which also recalcs all formulas that call volatile functions,
then evaluates the validation rule and takes appropriate action. In
that context, custom validation formulas are ALWAYS volatile no matter
what functions they call since validation rules are ALWAYS evaluated
upon entry.

Also, FWIW, if you select multiple cells, if the active cell doesn't
contain a validation rule but other selectedd cells do contain
validation rules, you could type anything you want in the active cell
and press [Ctrl]+[Enter], and Excel will happily enter the active
cell's value into all the cells without triggering validation in the
other selected cells. IOW, validation rules are only evaluated for the
active cell at the time of entry. And, as commonly known, validation
isn't triggered by pasting into cells with validation rules. If the
OP's users could be pasting values in from other programs (e.g.,
copying from PDF files), no validation rule will help. Only event
handlers and validation formulas in other cells provide relatively
robust validation. Excel's own Data Validation feature is an
unreliable toy, as industrial strength (NOT!) as internal passwords.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting alpha numeric data teetee209 Excel Discussion (Misc queries) 1 July 9th 08 01:14 PM
A validation rule on Alpha and Numeric characters Sar* Excel Worksheet Functions 11 June 11th 07 11:47 PM
How do I group alpha numeric data in excel? SlickOilSales Excel Discussion (Misc queries) 4 October 3rd 06 02:32 AM
can i write a macro to truncate alpha-numeric data? T-Dot Excel Discussion (Misc queries) 2 August 12th 06 08:15 AM
The colums changed from alpha to numeric how do you make it alpha worldmade Excel Discussion (Misc queries) 2 May 26th 05 03:44 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017