# Data Validation Letters and Numbers

#### forrestgump

##### New Member
Hi There, I want to create data validation so that the first 2 digits=PS and the next 4 digits will be numbers. I have tried a few things but I cant get anything to work? any help would be much appreciated.

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

=AND(LEFT(A1,2)="PS",MID(A1,3,4)+0,LEN(A1)=6)

This should do the trick...

=AND(LEFT(A1,2)="PS",ISNUMBER(MID(A1,3,4)+0))

This should do the trick...

=AND(LEFT(A1,2)="PS",ISNUMBER(MID(A1,3,4)+0))

I thought so too, but this
ISNUMBER(MID(A1,3,4)+0
will allow for only 3 numbers after PS

That's why I added the LEN=6

I thought so too, but this
ISNUMBER(MID(A1,3,4)+0
will allow for only 3 numbers after PS

That's why I added the LEN=6

Good point, but the OP didn't actually specify that the string should be limited to 6 characters.

Good point, but the OP didn't actually specify that the string should be limited to 6 characters.
hmm..
Hi There, I want to create data validation so that the first 2 digits=PS and the next 4 digits will be numbers. I have tried a few things but I cant get anything to work? any help would be much appreciated.
2+4 = 6

Perhaps it should be

Thanks everyone. I went with =AND(LEFT(A1,2)="PS",MID(A1,3,4)+0,LEN(A1)=6). The part of the formula I don't understand is the +0. What does this do?

Mid returns a TEXT string, the +0 converts that to a number.
And any number other than 0 works as a TRUE result for the AND function.

Thanks everyone. I went with =AND(LEFT(A1,2)="PS",MID(A1,3,4)+0,LEN(A1)=6). The part of the formula I don't understand is the +0. What does this do?
That will work as long as you do not get an entry similar in structure to these... PS9e34, PS+9e4, PS(12), etc.

Replies
1
Views
130
Replies
1
Views
89
Replies
6
Views
164
Replies
4
Views
59
Replies
25
Views
302

1,203,242
Messages
6,054,353
Members
444,718
Latest member
r0nster

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