# Cell Validation specific Alpha Numeric format returns false if in wrong format

1. ## Cell Validation specific Alpha Numeric format returns false if in wrong format

Hi,

Been googling for the last three hours with no joy.

What I am trying to achieve is that when a user enters a value in cell d3 it tells them they can't if it is not in the correct format

The format is as follows:

AS-1234-5678-9111

AA-NNNN-NNNN-NNNN

Also the first two letters will always be AS if it makes it any easier?

Any suggestions as I didn't realise it would be this difficult?

2. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Does this work?

=AND(LEFT(D31,2)="AS",MID(D3,4,4)+0>0,MID(D3,4,4)+0<=9999,MID(D3,9,4)+0>0,MID(D3,9,4)+0<=9999,MID(D3,14,4)+0>0,MID(D3,14,4)+0<=9999)
Custom Data Validation formula

Note that if they copy and paste into the cell Data Validation will not trigger and will accept what they enter (someone told me recently).

Are they inputting hyphens as well ?

3. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

I interpreted the request differently.

Assuming that you only want to allow strings with the following parameters:
1) Alphanumeric with only capital letters
2) Hyphens in character positions 3, 8, and 13
3) 17 character long
4) Starting with "AS"

Try this:

``Please Login or Register  to view this content.``
Edit: Just realized that this exceeds the formula character limit for DV formulas.
Working on a fix.

4. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

I just realized that I misunderstood the request and was making it way more complex than it needed to be.

Assuming these are the parameters:
1) Starts with AS
2) Hyphens at places 3, 8, and 13
3) 17 characters
4) Only numbers after "AS" (not including hyphens)

You can use this DV formula:
=AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(D3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

See attachment.

5. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Originally Posted by 63falcondude
I just realized that I misunderstood the request and was making it way more complex than it needed to be.

Assuming these are the parameters:
1) Starts with AS
2) Hyphens at places 3, 8, and 13
3) 17 characters
4) Only numbers after "AS" (not including hyphens)

You can use this DV formula:
=AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(E3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

See attachment.
Typo you have LEN(E3)=17

I was wondering about leading zeroes in each 4-digit number but you've covered that well!

6. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Thanks Special-K! Fixed.

7. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Works perfectly 63falcondude and thanks for your input Special-K.

Many thanks

Can I ask out of interest (not that it is relevant to this formula) if I wanted to say the first two letters must contain text and not fix it to AS how would I manipulate this part of the formula

``Please Login or Register  to view this content.``

8. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

First thought

AND(LEFT(D3,1)>="A",LEFT(D3,1)<="Z",MID(D3,2,1)>="A",MID(D3,2,1)<="A")

9. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

I'm assuming the last <="A" should be <="Z"

But it works perfectly.

Thank you both

10. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Here's another way:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW(\$65:\$90)),LEFT(D3,2))))=2

Glad we could help. Thanks for the rep!

Edit: The rows should be surrounded by INDIRECT to prevent this from changing if rows are deleted in the workbook.
That would look like this:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),LEFT(D3,2))))=2

11. ## Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

Originally Posted by Ratso
I'm assuming the last <="A" should be <="Z"

But it works perfectly.

Thank you both
Yep that's right
My glasses need cleaning

There are currently 1 users browsing this thread. (0 members and 1 guests)