Use data validation to force entries to be uppercase or lowercase

- [Instructor] Excel's data validation feature is a powerful tool for controlling what goes into a worksheet. And if we were about to set up a data validate rule in column A, wouldn't be a bad idea to first select column A, we could ultimately be creating a list in column A that will grow and grow and grow. When we go to data validation, it's found on the data tab in the ribbon, data validation, there are potentially lots of different restrictions we can set up by first going to the settings tab and then looking at the options in the panel under the word allow.

Lots of choices here. But no where in here do we see anything about upper and lower case. And there are lots of other features that you might be interested in to use as a basis for controlling what goes into a cell. So we don't see too many restrictions here. And certainly one we do not see directly is one about uppercase lowercase. We do however have a feature called custom and this can open the door to all kinds of different functions that we might use in our definition. I'm going to close this for now and talk about this idea about entries being uppercase or lowercase.

You can imagine in a situation talking about ID numbers. It's not uncommon for ID numbers to sometimes be all numbers or a mix of letters and numbers. And nearly always, if there were letters there, you'd want them to be uppercase. I'm going to type in an entry over in D9. Just imagine this is a code number. Call it W245t. You can see that the W is capitalized, the T's not. Regardless of the style of this, let's just say whenever we have letters we want them to be uppercase. Now there is a function called upper and by itself we can't do a whole lot with this.

If we do use this in referring to this cell, we will return that same entry, but the T will become capitalized. So that sort of at least makes us think about how we possibly could use that function. Now let's imagine that we want to see if whatever we type here is equal to the uppercase version of it. And so we might do something like this. Is the uppercase version of that data equal to whatever that data is. In other words, is that true. And when we press enter we see it is and that's not quite what we want to do.

You wouldn't necessarily know that there is a function called exact. Over in column F are the names of that function and some others which we will be using in this set of tips. So let's change this by using the exact function. As you type function names you don't need to capitalize them. I'm doing it here just for the sake of clarity. And when you do press enter they'll become capitalized anyway. We want to see if the uppercase version of whatever's in D9 is actually equal to D9. But we don't use an equal sign here. We put in a comma.

The function called exact is followed by two entries, just two. And we're trying to see if they're equivalent to one another. Are those the same. Is the uppercase version of what we see in D9 equal to D9? No it's not. Now if I were to capitalize the T over there, it certainly will be. So that's what we want to use over in column A if we want to make sure our entries are always uppercase. Now I'm going to copy this and make a change so I don't have to retype it all.

So I'll simply highlight all this, press control C and then escape. Now when you select a column and that's often the case when you set up data validation, you can certainly highlight just a certain range of cells if you wish but when we come to data validation and go to settings and choose allow, as I suggested earlier, none of these other options here fit however we can use custom. And I'm going to click in here and paste with control V. Now at the moment that I selected column A the active cell is A1.

And so in the formula that we use here, we use A1 even though literally we are not going to be making any changes to that cell. So it changes D9 entry to be A1. I'll do that twice. And I'll display this a bit larger in a moment. I'll simply copy that. Highlight it, press control C. So we're setting up a rule now so that every time we make an entry on column A, we want to check to see if its uppercase version is equal to what we've typed in column A. Click okay and I'll just paste this our here for reference.

With a leading space we can see the actual formula and control V. So that's the function being used. So I'll type in an entry here. Maybe these are all five digit, four digit. W1234 That's working fine, not a problem. Now I'll type the same entry with a lowercase W. And that's not acceptable. I could have customized this message. I didn't do that but if you click cancel, it completely wipes out the entry. Click retry, we're in edit mode and maybe I'll change that first W to capital W.

So you get the idea there. And that's just two examples, but you could explore that bit further. That's going to make sure that when we make entries in column A if they're not uppercase then we'll get a warning and we'll have to type it over or edit it. Now in this green box, and you probably saw this earlier, I want to add an additional restriction that not only must these be uppercase, they must be exactly six characters long. One thing data validation does not do is deal with existing data. The two entries that are already there are not six characters long.

We might not touch them for a while. If we try and make a change to them, if we don't make them six characters after we put in our new rule, we're going to have a problem there. So let's go back to data validation. I'm going to click column A. Back to data validation. And you might have seen earlier under allow, we do have a choice called text length. But if we use that, we can't use it directly in combination with the other restriction. So we're going to be doing this by way of the formula. And here too, I'm going to prepare the formula outside of data validation.

Preparing the formula outside of data validation is certainly not necessary. I'm doing it here for visual reasons so you can see it more readily. We still want this to be true, but we want to combine it with another feature. So I'm going to double click in here and use the function called AND. So I want two things to be true. As before I want the entry to be checked to see if it's uppercase. I want that to be true. So we want that the be there, comma. And what else do we want to be true? We want the length of the entry, and again we'll use cell A1.

We want that length to be exactly six characters long. Over in column F you see L-E-N. That means length, of course is the first three letters of that word but that's the function that allows us to determine the length of an entry in characters. L-E-N. Left parenthesis A1. Must be equal to six. So we want two things to be true and here's the formula we will be using. The new data validation formula in column A. So I'm going to put a leading space to this because I want it to stay there and look like that.

And then highlight this portion of it. Control C. And then go to column A and we'll put in the expanded data validation formula. Instead of the older one, control V. Paste it in here. Again as I suggested nothing about existing data is dealt with here. Those entries are still there. If I make that four or five, now if I make an entry, not acceptable because it's not six characters. I'll retry. So it might be a number at the end. It might be a letter. If it's a letter it must be uppercase.

If it's a number, fine not a problem. We're in a brand new entry. Is that going to work? That's six characters, it should work. If it's seven characters that's not going to work. So I think you can see how this is playing out. Now in column B it says must be lowercase. This is much less likely. I rarely see codes where they're lowercase or even lowercase letters are involved, but we could do that and once you've seen of course the example here I think is pretty easy as to how we might create this.

In fact I'm just going to copy this downward. And let's say in this example here we don't care about the length even. We'll take that out and we don't even need the AND function there to the left. But this time if we were to use this in column B we would use exact and of course instead of upper we use lower. And in this case B1. So we're setting this up ahead of time outside of the function simply to display more easily. So there we'll have it that way. Highlight this data right here, control C to copy it.

And then in column B we want these entries to all be lowercase, same general idea. Data validation. Allow. Custom. Formula, I'll paste it in with control V. Okay. So here's a code. That's not going to work because we got an uppercase W in there. I'll retry and put in a lowercase W and it does work. So we can test that with some other examples too. Again that's an unlikely code but that will work because it's all lowercase.

In other words no character there is uppercase. Now a variation on this and somewhat different is this idea of an entry must be numeric. There's a function off to the right that you might have seen or heard and you can probably guess what it does but is number. So for example, 234. Is that a number, equal is number. Referring to that cell is D23 a number? It is. What if it's D34Q? That's not a number. What if it's all letters? Well of course that's false too.

So this function here we could certainly use in column C if we wanted to make sure that every entry there is a number. So here too, I can adjust this ahead of time. I'll simply make that be C1 right now. And so we can paste that in directly. I'll highlight this, press control C to copy that. And then in column C data validation, allow, custom, and that formula, control V.

Any entry, and again we used C1 because it's currently the active cell, must be a number. Click okay. So three numbers and a letter, not acceptable. Maybe that T should have been a seven. There we go. And again we could test that with a few examples. Even though data validation has a lot of those built in options as we saw earlier, if you need to make entries uppercase, lowercase, or numeric as we've seen in these examples, you need to use different functions in excel to get the job done.