custom validation with number & text

  • Thread starter Thread starter aditya
  • Start date Start date
A

aditya

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
 
=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.
 
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)))
 
That'll accept entries like:

-00?1.1E10

I think the OP needs to better define this portion:
next digit should be a text
Click to expand...

I'm pretty sure they mean a letter from A-Z but does case matter?
 
Back
Top