Remember Me?

#1
May 25th 09, 10:44 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 54
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

#2
May 25th 09, 11:26 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058
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

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

#3
May 25th 09, 06:17 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572
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

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

#4
May 25th 09, 10:36 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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
zero to it and see if we get and error.
--
Gary''s Student - gsnu200854

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post aditya Excel Discussion (Misc queries) 1 May 25th 09 02:37 PM Antonio Excel Discussion (Misc queries) 2 April 22nd 08 05:08 PM Sloth Excel Discussion (Misc queries) 1 August 3rd 06 10:22 PM rbekka Excel Discussion (Misc queries) 1 June 19th 06 08:31 AM Frank & Pam Hayes Excel Discussion (Misc queries) 3 December 3rd 05 05:36 PM

All times are GMT +1. The time now is 10:58 AM.