Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 A-Z 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) |