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

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?

  #2   Report Post  
Old November 28th 08, 06:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default alpha numeric data validation for excel

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?


  #3   Report Post  
Old November 28th 08, 07:17 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

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?



  #4   Report Post  
Old November 28th 08, 07:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default alpha numeric data validation for excel

Biff,

thanks for the new ideas you gave me.

Kostis

On Nov 28, 9:17*pm, "T. Valko" wrote:
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?


  #5   Report Post  
Old November 28th 08, 07:40 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

Here is an even shorter formula that also seems to work...

=AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5)

--
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?






  #6   Report Post  
Old November 28th 08, 07:49 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

Glad to help.

I'm always looking for new ideas myself!

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
Biff,

thanks for the new ideas you gave me.

Kostis

On Nov 28, 9:17 pm, "T. Valko" wrote:
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?



  #7   Report Post  
Old November 28th 08, 07:51 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

Cancel that... this formula lets in unacceptable entries.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is an even shorter formula that also seems to work...

=AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5)

--
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?





  #8   Report Post  
Old November 28th 08, 07:57 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

Try entering this:

' 12345

That is: apostrophe<space<space12345

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Here is an even shorter formula that also seems to work...

=AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5)

--
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?






  #9   Report Post  
Old November 28th 08, 08:09 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

Your message was sent before you saw my second posting acknowledging the
formula didn't work... it lets lots of things through that the OP wouldn't
want.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try entering this:

' 12345

That is: apostrophe<space<space12345

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Here is an even shorter formula that also seems to work...

=AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5)

--
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?






  #10   Report Post  
Old November 28th 08, 08:40 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

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?






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 01:20 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