# Alpha Numeric Code - Data Validation

#### cameronb

##### Board Regular
I am looking to ensure that data is only entered into a column that follows the format "AB####" so the first code would be AB0001 second AB0002.

Is it possible to set data validation rules that would only allow the first two letters AB and then four numbers (ideally the next number from the preceding row)?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
You could use a custom validation in Data Validation:

=AND(EXACT(LEFT(A1,2),"AB"),ISNUMBER(--(MID(A1,3,255)))

But it sound like you need a formula rather to generate the next key.

Example, if A1 has the key, the next key in A2:

="AB"&MID(A1,3,255)+1

#### cameronb

##### Board Regular
I suppose I am looking for a data validation rule that will not allow anything other than the next logical code in the cell

i.e. if the preceeding cell had AB0001 then the cell will only allow AB0002

#### Jon von der Heyden

##### MrExcel MVP, Moderator
But the point I was making was that you could make it easier for a user by having it auto-generate the key, rather than expect them to manually enter it. That was the point of the 2nd formula that I provided.

#### jasonb75

##### Well-known Member
You can't validate increments in alpha-numeric codes directly with DV, but you can with a helper cell.

Somewhere safe, maybe a in hidden row at the top of your sheet, enter the formula

="AB"&TEXT(COUNTA(C2:C25)+1,"0000")

C2:C25 was my test range, this should refer to the range that the validation should apply to.

Apply the data validation rule to the same range, using allow = list, source will be the cell with the formula above, must be set absolute, i.e. =\$A\$1

If you allow in cell dropdown you get the next in the series generated by the DV

Replies
2
Views
521
Replies
5
Views
659
Replies
3
Views
652
Replies
7
Views
206
Replies
1
Views
537

1,191,204
Messages
5,985,257
Members
439,953
Latest member
suchitha

### 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.

### Which adblocker are you using?

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

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