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

Dobbsylondon

New Member
Joined
Oct 21, 2014
Messages
6
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
ooof
this took some time :)
This formula should evaluate to TRUE if all conditions you need are met:

=AND(OR(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(LEFT(A1,1))>=97,CODE(LEFT(A1,1))<=122)),ISNUMBER(VALUE(MID(A1,2,4))),OR(AND(CODE(MID(A1,6,1))>=65,CODE(MID(A1,6,1))<=90),AND(CODE(MID(A1,6,1))>=97,CODE(MID(A1,6,1))<=122)),OR(AND(CODE(MID(A1,7,1))>=65,CODE(MID(A1,7,1))<=90),AND(CODE(MID(A1,7,1))>=97,CODE(MID(A1,7,1))<=122)),LEN(A1))

I tried using data validation on A1 with it but it seems to be too long for it.
If you place this formula in a cell it will tell you if the format is met or not.

Maybe someone can improve it or give some other idea e.g. use a macro to do the validation
 
Upvote 0
Last edited:
Upvote 0
Unfortunately - operators such as {} do not ssem to be allowed for use in Data Validation

However - a shorter version of my previous post based partially on Rick's idea

=IF(LEN(A1)=7,AND(OR(AND(CODE(MID(A1,{1,6,7},1))>=65,CODE(MID(A1,{1,6,7},1))<=90),AND(CODE(MID(A1,{1,6,7},1))>=97,CODE(MID(A1,{1,6,7},1))<=122)),ISNUMBER(VALUE(MID(A1,2,4)))),FALSE)


Limitation is - all characters to be the same case (all UPPER or all lower).
Still can't figure a way to use it in data validation w/o macro.
 
Upvote 0
Have you tried this in the custom formula of the data validation box? Where E1 is the cell you are applying the validation to

=AND(LEN(E1)=7,ISTEXT(LEFT(E1,1)),ISNUMBER(VALUE(MID(E1,2,4))),ISTEXT(RIGHT(E1,2)))

or have I missed something ?

I have missed something .... istext accepts numeric values :(
 
Last edited:
Upvote 0
yes - istext() treats an alpha-numeric sting as text
 
Upvote 0
Try if it works with 4 functions:

=SUM(ISNUMBER(1*MID(A1,{1,2,3,4,5,6,7},1))*{1,10,10,10,10,1,1})+100*(LEN(A1)=7)=140
 
Upvote 0
Try if it works with 4 functions:

=SUM(ISNUMBER(1*MID(A1,{1,2,3,4,5,6,7},1))*{1,10,10,10,10,1,1})+100*(LEN(A1)=7)=140
Your formula will accept non-alphanumeric characters in the first, sixth and seventh positions; for example, it will return TRUE for "/1234-/" (without the quotes, of course).
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top