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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com