Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 25th 09, 10:44 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 54
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
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
custom validation number and text aditya Excel Discussion (Misc queries) 1 May 25th 09 02:37 PM
Custom Data Validation for specific text and numbers only Antonio Excel Discussion (Misc queries) 2 April 22nd 08 05:08 PM
Data Validation and Custom Number Formats Sloth Excel Discussion (Misc queries) 1 August 3rd 06 10:22 PM
Data validation with custom number formatting rbekka Excel Discussion (Misc queries) 1 June 19th 06 08:31 AM
Custom Number Format Text 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.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017