Home 
Search 
Today's Posts 
#1




custom validation with number & text
how can i write formula for custom validation for a cell like this.
first 3 digit shoulg be a number, next digit should be a text then next 6 digit should be number. e.g. 126P151008 thanks in advance 
#2




custom validation with number & text
=AND(NOT(ISERROR(LEFT(A1,3)+RIGHT(A1,6))), ISERROR(MID(A1,4,1)+0))
the trick is that we can test if a set of characters is a number by adding zero to it and see if we get and error.  Gary''s Student  gsnu200854 "aditya" wrote: how can i write formula for custom validation for a cell like this. first 3 digit shoulg be a number, next digit should be a text then next 6 digit should be number. e.g. 126P151008 thanks in advance 
#3




custom validation with number & text
I think you'll need a length verification.
Otherwise, your formula would accept this: 126PQR151008 Such as: =AND(LEN(A1)=10,ISNUMBER(LEFT(A1,3)),ISNUMBER(RIGHT(A1,6)),ISERR(MID(A 1,4,1)))  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Gary''s Student" wrote in message ... =AND(NOT(ISERROR(LEFT(A1,3)+RIGHT(A1,6))), ISERROR(MID(A1,4,1)+0)) the trick is that we can test if a set of characters is a number by adding zero to it and see if we get and error.  Gary''s Student  gsnu200854 "aditya" wrote: how can i write formula for custom validation for a cell like this. first 3 digit shoulg be a number, next digit should be a text then next 6 digit should be number. e.g. 126P151008 thanks in advance 
#4




custom validation with number & text
That'll accept entries like:
00?1.1E10 I think the OP needs to better define this portion: next digit should be a text I'm pretty sure they mean a letter from AZ but does case matter?  Biff Microsoft Excel MVP "Ragdyer" wrote in message ... I think you'll need a length verification. Otherwise, your formula would accept this: 126PQR151008 Such as: =AND(LEN(A1)=10,ISNUMBER(LEFT(A1,3)),ISNUMBER(RIGHT(A1,6)),ISERR(MID(A 1,4,1)))  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Gary''s Student" wrote in message ... =AND(NOT(ISERROR(LEFT(A1,3)+RIGHT(A1,6))), ISERROR(MID(A1,4,1)+0)) the trick is that we can test if a set of characters is a number by adding zero to it and see if we get and error.  Gary''s Student  gsnu200854 "aditya" wrote: how can i write formula for custom validation for a cell like this. first 3 digit shoulg be a number, next digit should be a text then next 6 digit should be number. e.g. 126P151008 thanks in advance 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
custom validation number and text  Excel Discussion (Misc queries)  
Custom Data Validation for specific text and numbers only  Excel Discussion (Misc queries)  
Data Validation and Custom Number Formats  Excel Discussion (Misc queries)  
Data validation with custom number formatting  Excel Discussion (Misc queries)  
Custom Number Format Text  Excel Discussion (Misc queries) 