Excel Custom Data Validation (Use formulas to check for text, numbers & length)

in this tutorial I'm going to show you a way to create complex data validation checks in Excel so that you can make sure the users of your file who are inputting data in your template that they don't make any mistakes especially if you need to make sure that they input the correct combination of text and numbers in a Cell so in the previous video we covered the basics of excels data validation if you miss that I'm gonna put the link to it below the video but what if we have more complex cases [Music] to get a hang of how excels custom data validation works let's try and solve for these three cases in the first case we want to create a data validation right here where the user can input company code but we have to check that the first character is any text followed by four digits how do we do this check we can use a formula for it so if we go to data validation right here under in the data tab on data validation we can select custom and we can input our formula right here but I'm not going to do that I'm actually going to input my formula in the cells right here because when it inputted in the cells I get Excel help with my arguments right so whenever you're dealing with more complex formulas it's always easiest to write it in the cell first and then once you're happy with it copy and paste it in here okay so let's go back and think about our formula we need to do a few checks here what are those checks well one check is that we need to make sure that the total number of characters equals five gray so that's one check so I'm just gonna write them here and we can do each single check separately and then we can combine them into one check what's the formula for this one well we can use the Len function right that gives us the number of characters into text we're gonna have our data validation here so I'm just going to click on this close bracket press enter so now let's just put in some dummy text here so I'm going to put C 2 3 4 5 ok so total number of characters is 5 ultimately I want these to result in true and false values so I'm actually going to write out the test here blend a 5 equals 5 okay in this case it's true if I delete any of these numbers it's going to revert to false okay what's the second test let's do the numbers first we want to check that the last four characters are numbers which also means that the combination of these four numbers must be a number which formula can they use to strip out the last four characters I can use the right function I just have to click on my text and decide the number of characters I want to show from the right-hand side of the full text here and I want to get four out I see my number here and I could check if this is a number right that's something that's going to give me true and false values so let's wrap this up in the is number formula now I get a false here so it doesn't think two three four five is a number why well let's take a look at it I'm gonna highlight this and press f9 we can see our number but it's inside quotation marks it actually sees it as text I'm gonna press controls that to go back to turn this into a number we can perform some mathematical operation on this so we could multiply by one we could add a zero to it or there is also a function that allows us to do that it's called the value function we could wrap this up in the value formula okay so now let's check this it's true it does see it as a number right so if I change one of these to let's say a character I'm going to put teen there it's fall okay so this part is fine as well now for the last check we want to see if the first character is non-numeric there is a function in Excel and it's called is text but similar to is number this text is also going to return yes four numbers inside attacks so I'm just going to keep it simple and instead of checking if it's text I'm just gonna check if it's not a number so if it's not numeric but first let's strip out the first character of this what formula can I use here the left function that's my text I just want to get the first character out so I put one in there and I get a C now if I put this in is number and again I put the value function in there yes I need another bracket I get false I want this to revert to true if it is text so I'm just gonna put not so basically if it's not numeric then it should return a true so let's just test this if I change this to a number this one is false if the total number changes this one is false so basically the way we can bring these together is with an and function only if each single result reverts to true this means that the value input has passed the validation to combine them to one formula I'm just going to activate my clipboard click on this little icon here you can also do the shortcut key ctrl C C if you already have a tick mark here so let's start copying just going to press ctrl C on this press escape to leave and repeat the same thing for the other formulas right here I'm gonna combine them all together I'm going to say equals and and go through my clipboard so just click on this Excel separator click on this operator and click on this close the bracket press ENTER ok so that's my final formula I'm gonna highlight copy press escape go to where I want to have my data validation go back to data data validation select custom and paste in the formula let's check that doesn't work that doesn't work let's just put all numbers as well that doesn't work that works ok so once I'm happy with it I can copy and paste my data validation to wherever I want so I'm just going to highlight these cells right mouse click paste special and select validation I can also add in and input text here so going back to the data validation I can put an input message here just to make it clear what the people should input and also why they're getting an error and this is what I showed you how to do in the basics of data validation so I'm just going to move on now to the next one here we want to have the first two letters fixed and we want them to be PT followed by four digits how could we set this one up in this case our checks are similar to this one right except that the full length of this should be 6 again I'm just going to put one example here so let's say PT 4 5 3 4 that's one check the other check that the last 4 characters are numeric that's the same so I'm just going to copy and paste this here now the only thing that's quite different to what we did before is that we are actually checking what the first two characters should be so what formula could we use here we can use the left function but this time we're going to make sure that the two characters from the left-hand side equals PT ok and that's it I'm just going to clear these and copy and paste them back to my clipboard now again I'm gonna combine them with the ant function so just click on this excel separator click on this and click on this one what was the next step we need to copy our formula press escape go to where we want to have the data validation go back to data validation settings under custom we're gonna paste in our formula okay so let's just quickly check if I have TG okay so PT any number it's gonna do the job now obviously once you're done with this you're gonna remove all of these helper cells this is just to help you write the formula for the custom validation next one for this case the first two letters can be anything how do we account for that the part that's the same I'm just gonna copy and paste here for the last check where we're checking for the two letters how do we do that could I actually just use this formula that we originally had where I'm just checking the one letter could I just update this to check two letters and see if they're not numeric let's try that basically everything is pretty much identical to what we did in the first case except our length is six instead of five the only other thing I changed is that we're gonna check the first two characters and see if they're not numeric I'll put TT and a number this looks good I'm gonna change these to two numbers this looks good because the last one should give me false but now what if I have one letter and one number this should also give me false but it doesn't it gives me a true and the reason for that is that when I take this part and I press f9 on it I get false because the combination of B 4 is not numeric and that's the problem here what I could do is to add another condition here and check for the second character and make sure the second character by itself is not numeric I'm going to keep the original condition the same I'm just going to check the first character right so that shouldn't be numeric but now I'm gonna do a similar thing for the second character except here there is a better function for this the mid function the mid function allows us to take any specific text from this whole text here and we can decide where we want to start first argument is our entire text then we can specify where we want to start where do we want to start now number two right the second character and then we can decide how many characters we want to strip out we just want to strip out the second character only so I just need one character and here I get a four so now I can do a similar thing like in this formula use naught is number and value so I'm gonna be a little bit lazy and copy this part and paste it in here and I need a few brackets and one more okay so now I get one false here which means that my full combination is going to be false if the first one is a number and the second one is not this one is going to be false and if both are numbers they're both going to be false right so you can also use other array functions here so you could use the aggregate function but the data validation box doesn't like that in there directly so you'll have to go through name manager and then to data validation writing it in this way avoids your using an extra step of going through name manager okay so again we need to do the same thing I'm just going to copy these formulas combine them with an ant function and just go through my clipboard and then close the bracket this entire formula I'm gonna copy it go to where I want to have my data validation go back to custom and paste my formula so now let's check this out it's good there's a problem that looks good okay so that's how you can use custom data validation to add more complex checks inside the templates that you send out to your users if you found this video useful I'd appreciate it if you gave it a thumbs up and for more videos like this one don't forget to press the subscribe button so that you can get updates when new videos come out [Music] you

Channel: Leila Gharani

Views: 123,920

Rating: 4.9476695 out of 5

Keywords: Excel custom data validation, Excel data validation, excel len, complex, validation formula, excel right, excel mid, Excel data Validation with formula, excel left, check for length, Excel dropdown list, check for text in data validation, data validation with text and numbers, XelplusVis, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, excel length formula

Id: bDXQy60BcT4

Length: 14min 16sec (856 seconds)

Published: Thu May 24 2018