Original subtitles

In exercise, 11, I'd like you to practice creating custom data validation.

So for this exercise, we want people to be able to type in a part number, but that part number must

meet certain conditions and those conditions are that the part number has to be six characters long.

And the first three characters are letters, and the last three characters are numbers.

So if anybody tries to enter anything in that doesn't meet those rules, they're not going to be able

to input it into the cell.

Now a couple of things before you get going on this, I've already put the headings up here just kind

of start you off in the right direction.

If you recall, first, we needed to use a formula to work out the length of the plot number, and then

we changed it to a true or false value.

Because what we're aiming for here is for all three of these to be true when the part numbers been entered

correctly and false when something's been entered incorrectly.

So we need to find the length of the part number and then convert that to a true or false result.

We need to extract the first three characters and make sure that those are letters.

And then we need to extract the last three characters and make sure that those are numbers.

Then we need to combine all of the formulas that we've created together before pasting it into our data

validation.

And remember, you can use the clipboard to help you when you're copying your long formulas.

And if you want to, if you find that pretty easy as an optional extra, you can add an error message

into your data validation rule.

So give that a go.

If you'd like to see my answer, then please keep watching.

So the first thing I'm going to do here is we're going to grab the length of the part number.

So for this, we're going to use the lens function.

Let's select the part number and that is going to return the result of six because we have six characters

now, I effectively need to turn this into a true or false result, so I need to make this a logical

formula.

So what I can say here is is the length of the Pont number equal to six.

If it is, I'm going to get a true result.

If I add another number onto the end, it's going to be a false result.

So we're looking good so far just that.

Now the next thing we need to do is we need to check that the first three characters that have been

entered are letters.

So let's extract those three characters from this string first.

We're going to type in equals left, going to select the text and we want the first three characters.

So that is given us a b c.

Now we need to change this into a true or false result.

So the first thing we're going to add into here is we're going to ask the question Is this a number?

Let's add that on to the front and close of the bracket.

Now, currently, this is saying false because Excel thinks that this number is text, so this is where

we need to convert it into a value instead.

So we're going to add value in here and of course, close off another bracket, and it's still saying

that it's false.

We kind of want to switch this around.

So it's the opposite because this is effectively a number.

So we're going to go up to the form in a bar and we're going to add in knots, which will basically

do the reverse.

So now we get true, we're going to do a similar thing to extract the last three characters and check

to see if those are numbers.

So we're going to say equals right.

We're going to select the text and we want to extract the last three.

Now we're going to do the same thing.

We're going to go up.

We're going to say is no, and we're going to convert it straight into a value as opposed to text.

I now need to add two brackets on the end and hit enter.

So now notice that all of these are true when the part number has been entered incorrectly.

If I change anything in here, so if I change this two numbers this first part, this is going to change

to false first three characters are letters that is now false.

If I double click and let's say a b c d e f, this one is now going to change to false because the last

three characters are not numbers.

And if I add something extra onto the end, this one is going to go to false because it no longer is

six characters long.

So let's put this back to ABC one two three.

All of those are true.

So now we have all of our formulas, we need to combine them together.

And for this, we're going to open up the clipboard.

Let's clear out everything that we have there.

I'm going to double click that, select the first formula without the equals control c to copy to the

clipboard, double click Select Control C and then finally, double click that select everything control.

See, now we're going to combine those together with an end function.

So all we need to do here is paste, comma paste, comma paste.

Add another close in bracket hit enter and our result is true.

So now that we have this big, long formula working correctly, we can copy and paste this into our

data validation.

So let's select it Control C to copy, and we want to apply this data validation to sell a five up to

data into data validation.

And we want to do custom data validation and control V to paste in our formula.

Click on OK.

So now we can pretty much get rid of all of this that we have in here, and I'm just going to clear

those cells.

So now if I change this to ABCD F, I can see that that's not working.

If I change it to one two three four five six, that's also not working.

But if I change it to d e f one two three, that's going to work because it meets all of the rules.