Controlling a cells format - Alpha Numeric (x4) Alpha Alpha

[Dobbsylondon]

Good day all,

First post, never lurked.

I'm confident you guys are the ones to ask though.
In an existing sheet, I'd like to control the format of cells in a column. Namely restrict it to ANNNNAA (A=Text and N=numbers), 7 characters maximum as it's really meant to accept only existing unique ID numbers.

I've tried to use \####\\ or "",####"","" which may or may not obviously work as a custom. As my understanding is at it's limit, I thought it time to ask for some help.


Thank you all in advance and I hope to repay the favour at some point.

Kind regards

 

[Rick Rothstein]

Would I change A1 to (in the case of my sheet) B:B to apply it to the whole B column?

The formula I posted was missing some $ signs to make the ranges absolute so that when applied against multiple cells, they would remain constant (the formula below is now correct). Select the whole column, if that is how many cells you think will ultimately need this validation rule (if you select a smaller range, make sure to start with B1 to match the cell reference in the formula), then call up the Data Validation dialog box, select Custom from the Allow drop down and put the following formula in the Formula field...

=IF(LEN(B1)=7,AND(IF(MID("A0000AA",ROW($1:$7),1)="A",(MID(B1,ROW($1:$7),1)>="A")*(MID(B1,ROW($1:$7),1)<="Z"),ISNUMBER(-MID(B1,ROW($1:$7),1)))))

 

[mikerickson]

Perhaps
=ISNUMBER(MID(B1,2,4) / (LEN(B1)=7)/(0=SUMPRODUCT(--EXACT(UPPER(MID(B1,{1,6,7},1)),LOWER(MID(B1,{1,6,7},1))))))

 

[Rick Rothstein]

Perhaps
=ISNUMBER(MID(B1,2,4) / (LEN(B1)=7)/(0=SUMPRODUCT(--EXACT(UPPER(MID(B1,{1,6,7},1)),LOWER(MID(B1,{1,6,7},1))))))

You cannot use that in the Data Validation that I think the OP wants to use because of the array constants.

 

[Dobbsylondon]

You cannot use that in the Data Validation that I think the OP wants to use because of the array constants.

Thank you good sir #tipped_hat

 

[Rick Rothstein]

Thank you good sir #tipped_hat

Just double checking... did you see Message #21 yet?

 

[snb_]

Arrayformula:

=SUM(--ISNUMBER(--MID(B1;ROW(2:5);1));--ISNUMBER(--MID(B1;ROW(6:7);1));--ISNUMBER(--LEFT(B1;1)))=4

 

[Rick Rothstein]

Arrayformula:

=SUM(--ISNUMBER(--MID(B1;ROW(2:5);1));--ISNUMBER(--MID(B1;ROW(6:7);1));--ISNUMBER(--LEFT(B1;1)))=4

@snb,

You need to patch this formula so that it will not accept values like A123E4CD, A1X123CD, A1234ABCDEF, etc.


@Dobbsylondon,

Still checking... did you see Message #21 yet?

 

[snb_]

Not that complicated:

=SUM(--ISNUMBER(--MID(B1;ROW(2:5);1));--ISNUMBER(--MID(B1;ROW(6:7);1));--ISNUMBER(--LEFT(B1;1));LEN(B1))=11

 

[Dobbsylondon]

@snb,

You need to patch this formula so that it will not accept values like A123E4CD, A1X123CD, A1234ABCDEF, etc.


@Dobbsylondon,

Still checking... did you see Message #21 yet?

Did indeed, outstanding stuff.

Thanks again to all who took the time to reply.