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

Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Enhance your Excel skills with this detailed tutorial on creating complex data validations. Ideal for ensuring accurate data input in your Excel templates, this video is a must-watch for those needing to enforce specific text and number combinations in cells.
🌟 Key Learning Points:
- Custom Data Validation: Learn how to use Excel's custom data validation feature for complex checks.
- Formula-Based Validation: Discover how to construct formulas to validate specific data formats, such as a text character followed by numbers.
- Practical Examples: Get step-by-step guidance on setting up validations for different cases, like ensuring a specific character count or a fixed text prefix.
- Advanced Formula Techniques: Understand the use of functions like LEN, RIGHT, ISNUMBER, and MID to create robust validation criteria.
- Error Prevention: See how custom validations can prevent incorrect data entries and maintain data integrity.
- Input and Error Messages: Learn to add helpful input and error messages for users, enhancing the usability of your Excel file.
🚀 Practical Applications:
- Create templates that ensure users enter data correctly.
- Apply complex validations for data consistency in professional Excel reports.
- Enhance data integrity in templates shared within teams or organizations.
We also cover Excel's LEN, RIGHT, LEFT and MID functions.
Full Article: www.xelplus.co...
★ Links to related videos: ★
Basics of Data Validation: • Create SMART Drop Down...
★ My Online Excel Courses ★
Courses ► www.xelplus.co...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Stay ahead with our weekly newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

[@darrengodkin]

Love the way that you just broke down in small manageable chunks an excel problem. Like the use of the clipboard too, great tip!

[@LeilaGharani]

Thank you Darren. I'm glad you like the presentation. Yes - the clipboard is great. It works for Word and PowerPoint too.

[@signalmas5673]

The seed is planted. Never know when one needs another card from your sleeve. Clipboard thing, first time i see it, will try soon, looks very useful. Thank you Leila, always a pleasure.

[@LeilaGharani]

That's great! I'm glad you found something new here. Thank you for your support.

[@emrulhasan1705]

3rd condition I did it myself without watching the video, though it took long time. I failed many times but eventually I figured it out. Little sense of accomplishment but definitely I enjoyed it a lot. Thanks Leila, you are awesome.

[@mike3584]

Hi Leila, Thanks for the good video again. Thanks for that I have been enjoyed watching it again. Just one little thing, if we didn't change any thing in a dialog box, like the Data Validation dialog box, it is better much to click the 'Cancel' button to close the window/dialog box than to click the 'Ok' button. The reason being is that when 'Ok' is clicked, it would trigger a whole string of programming functions/classes to be called to complete that action, while 'Cancel' button would not trigger any of those. Of course, on the surface, it looks the same but it is not in terms of using system resources and efficiency. Once again enjoyed your video as always. Please keep up with the good work Leila.

[@muhammetgylyjov9288]

Greatest tutorial about data validation, Thank you very much for your effort!!!

[@ElGordoFreeman]

In your first example, a code like ";1234" would be valid. You should use a function like =AND(CODE(UCASE(letra))>=65,CODE(UCASE(letra))

[@mazharabbasbukhari7390]

i guess you also start some tips lessons.

[@paulcocuzzo6213]

Manuel, your example inspired me to try using the letter values more explicitly: =(LEFT(A5,1)>="A")*(LEFT(A5,1)

[@sachinrv1]

Lovely..and very useful. I prefer key-board short-cut ALT+D+L and then TAB or SHIFT+TAB to move around in data validation dialogue box. For those who are not well-versed with clip board; they can simply hit a space bar at the beginning of the partial function (so that their efforts are not lost) and then continue with the next leg. After completing individual functions combine them step-by-step in AND function. Thanks for upload Leila :)

[@LeilaGharani]

Thank you Sachin for the pointers and the shortcut keys :)

[@Pankaj-Verma-]

Full of value! As always! Will never see Data Validation the same way!

[@LeilaGharani]

Glad it is helpful! It can be a powerful tool.

[@TheLcsvince]

Thank you. Your tutorial guides me thru writing the custom DV formula.

[@michaeldiamond2726]

Hi Leila, Great video as always. When I first saw you using the Value function to convert text to numeric I was reminded of Mike Girvin's videos where he used a double negative (- -) to do the trick. I looked at your comments from other views and saw this was option was already suggested a few days ago. Another thought that came to mind was to use named ranges (or named range) in place of the actual formulas that would be entered into the custom box. This would save real estate in the data validation box but it demonstrates how you could say the same thing in Excel (get the same results) but just in another way.. The more I watch videos like yours, Bill Jelen and Mike Girvin I'm becoming more and more convinced Excel is an language unto itself. If you think about it, in Excel you do have 5 or 6 languages that exist presently (e.g. formulas, custom formatting, the M language, Dax, VBA, ASCII, etc). Would it be possible to create a series of videos that covers all aspects of data Validation? I normally only use the list box feature and I think I might be missing out on other functionality it can provide in my spreadsheets. I also wonder if you could create a video on actual spreadsheet design. I'm always floored by your Excel files in the manner in which you set them up. They look so neat, clean and organized. Not all cluttered like my spreadsheet tend to be (with too many colors, etc). Michael

[@LeilaGharani]

Hi Michael - thank you for your comment. In the validation basics video, I cover the other aspects: kzbin.info/www/bejne/fIPMd5mBl8R1laM - do you mean this? :) - I always wanted to do a course / videos on spreadsheet design - I have to think about that....maybe some case studies here would be great...

[@dataisfun4964]

Ma, can I say you're awesome you seem to have solution to every excel problem I encounter. 👍 👍 👍

[@sanjeevtyagi7471]

And I thought I know data validation quite well...but this something different and innovative..good work Leila

[@sazzarello]

That was great...a big thumbs up!...exposing and using the clipboard that way was really great too.

[@09shirish]

Every time your tutorial get me some useful ideas ! Thanks a lot !

[@LeilaGharani]

I like that :) You're very welcome.

[@09shirish]

@@LeilaGharani Sirji i've created data validation list in excel sheet, the list is appear on my laptop, but i copy the same file on my pc the tag which is not display ! pl told me solution for appearing this list ! If any setting in excel, pl told me ! Your guidance is my learning point ! Mam pl reply

[@atiaabdelkader1905]

This is the best I have ever seen thank you very very much, I recommended this for my students

[@MohammadAshooryan]

as always you're method of teaching is very awesome. thanks a lot for sharing your knowledge.

[@LeilaGharani]

Thank you Mohammad and also for your support.

[@chillpill7348]

Excellent.. as usual.. Great learning. Thanks for such a nice tutorial.

[@barttitulaerexcelbart9400]

Nice presentation of many text functions. But I really liked the clipboard functionality. In never used it, but I will give it a try. Very useful for nesting formulas.

[@LeilaGharani]

Thank you Bart. Agree - that's a great functionality. Not just for Excel but for the other office applications as well :)

[@jodyhollis]

Yes, I have been doing this for 25 years, and I never saw that. Very clever.

[@PelpsRoxXx]

That didn't work for me... I'm using Excel 2021

[@mdzahir1512]

Really helpful step by step tutorial. Thanks a lot.

[@LeilaGharani]

My pleasure, glad to help.

[@shajimanghat1500]

hats off to your training - wow

[@karenmarites5735]

All of your videos are very useful, Leila.

[@LeilaGharani]

Glad to hear that, Karen!

[@hanskdavis]

you make all of this seem sooooo easy. thank you!!

[@sudheerkumar-jt3gm]

Hi Leila, You speech is very good and your English also very understandable.. thank you for your uploading valuable videos..

[@LeilaGharani]

Thank you for your support. I'm glad you find the videos useful :)

[@tracyojila]

@@LeilaGharani very useful ma

[@Passion4Fashion2023]

You made me like Excel 👍.

[@santoshpv321]

Found this really helpful. Thx.

[@yulinliu850]

Big thank-you, Leila! Superb channel on KZbin.

[@LeilaGharani]

Very happy you like the channel. Thank you for your support.

[@vitietajonera4159]

Thanks a lot for this informative video!

[@vida1719]

As always excellent step by step explanation

[@LeilaGharani]

Thank you Vida.

[@mohitdhanwani5540]

Thanks ma'am...its always worth watching your video.

[@LeilaGharani]

You're very welcome Mohit :)

[@AbhishekTripathidce]

i really appreciate your effort and wanna say thank you for this very informative and useful tutorial.

[@LeilaGharani]

You're very welcome Abhishek!

[@HishamKasim]

Nice.. thanks for toturial... 👍

[@TheMuraleee]

Love you mam, you helped me a lot... I keep on watching your teaching...

[@LeilaGharani]

It's my pleasure

[@RJYL]

Super great for Excel validation.

[@TheJoshtheboss]

Just wonderful. So happy there are people like you on KZbin. Is there a way to add validation for specific number of decimal places allowed in a cell?

[@holopanen]

Just another great video!

[@satyenpurohit3947]

It is videos of this type i always keep an eye on your channel... Thanks for ur beautiful video

[@LeilaGharani]

Thank you! I'll make sure to add in more of these.

[@incognitootingocni4743]

FANTASTIC! THANKS!!!

[@lauraalkula2206]

Thank you so much! 🥰

[@LeilaGharani]

You’re welcome 😊

[@dawudayunusfarouk3098]

Leila Gharani, Magnificent presentation. I don't see much of your VBA.

[@LeilaGharani]

Hi - Thank you. Last week was VBA :) - I made a VBA playlist, ,make sure you check it out. I'm altering Thursday videos for now between Excel and VBA.

[@VenemAcademy]

Thnx 4 this usefull developer tip to split the validation in simple steps and combine it via the clipboard since the validation window is so hiddious for editing :-|

[@shashikkantlohar1163]

Hi Leila...you are awesome in Excel and you are a great explanator...thanks for your handy tricks and tips...

[@FRANKWHITE1996]

Great tip with Ctrl+C+C! 🙏 🙌🙌🙌

[@anshuiway]

Waw mind blowing. This is the most awaited Data Validation formula which I am writing for this. Thank you for this @Leila

[@LeilaGharani]

You're very welcome :)

[@DougHExcel]

Excel-lent examples for the custom validation!

[@LeilaGharani]

Thank you very much Doug.

[@oguzatalay3381]

Amazing. I didn't know that before this video. Thanks for good content

[@sherko965]

you are brilliant,amazing,fabioulus

[@Xlorate]

Great explanation! I just used it for 00/0000. 2 numbers "/" 4 numbers =AND(LEN(A1)=7,ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE((RIGHT(A1,4)))))

[@TheYogShakti]

Hi can you help me for format XX 000 000 its really urgent

[@kumardilip5255]

Nice Vedio Leila....thank you so much for explaining very well 👍👍💐

[@LeilaGharani]

You're very welcome. Glad you find the explanations easy to follow.

[@rabar25]

Wow .. amazing tutorial

[@GiteshBajaj]

Thank You

[@rnsraja]

Good learning today

[@LeilaGharani]

Happy to hear that :)

[@brandonpillay9025]

Nicely done!

[@LeilaGharani]

Thank you Brandon

[@chamindabasnayake4844]

Thank you! very useful & very intersting

[@LeilaGharani]

You're very welcome Chaminda.

[@keithr2835]

Exactly what I needed to know! Thank you so much!!

[@theofficialseries]

Very nice 👌 validation,

[@muinuddin6559]

Thanks u so much

[@miguelbello82]

Very usefull this video, thanks for sharing

[@LeilaGharani]

You're very welcome Miguel. Thank you for your support.

[@chandraprakashs4814]

This session is helpful, is it possible to get expand & collapse option in data validation list of we have multiple header & subgroup

[@yaashjut76]

this one is superb. lovely

[@stevennye5075]

very helpful

[@johnborg6005]

Thanks Leila. This was very interesting!!! :) :)

[@LeilaGharani]

You're very welcome John :)

[@hosseinhosseinpoor4845]

thanks thanks thanks.

[@ococino]

mic drop! Brava! 👍

[@kassimbusari]

Thanks. Alot!

[@balavanangamudik9213]

Extraordinary Madam, Great

[@HarmeetKaur2711]

I am just a Fan of you

[@LeilaGharani]

Really appreciate your support! Thank you.

[@samuelkodjoe1645]

thanks for the insight.....very helpful....got u!!

[@LeilaGharani]

Glad you like it Samuel.

[@user-eq7dk2lk2d]

thank you)*(thank you) ** 1000 for all thinks you do it queen)

[@LeilaGharani]

You're very welcome. Thank you *1000 for your support of my channel.

[@richardmiranda894]

Data validation for date and time of the cell

[@user-zr1gk7ho6y]

Keep it simple: AND("A0001"

[@HHH-nv9xb]

Data mining is such a pain. It has to do with lack of foresight in planning. Thanks for teaching an old dog new tricks.

[@HOLLYWOOD003]

Gud presentation.. Keep it up..

[@I_Follow_Jesus]

Great video. You explain things well enough for your viewers to experiment and learn rater than just copying exactly what you're doing. I was playing around with the first example and found the first character could be a punctuation character and still pass validation. My solution was to replace the "=NOT(ISNUMBER..." argument with "=OR(LEFT(A5,1)="A",LEFT(A5,1)="B",LEFT(A5,1)="C")...LEFT(A5,1)="Z"). This worked, but, it seems like there should be an easier way to enforce the first character is a letter. How would you suggest handling this?

[@LeilaGharani]

Hi Phillip. Thank you! Yes - you're right, anything that's not seen as a number is considered text for the 1st character. One way I can think of restricting it to the alphabet would be to use this formula ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")) so the entire formula would now be: =AND(LEN(A5)=5,ISNUMBER(VALUE(RIGHT(A5,4))),ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

[@I_Follow_Jesus]

Leila Gharani Thanks.

[@mike3584]

@@LeilaGharani Well done Leila! Looks like we definitely need a new built-in function called isalphabet() 😊

[@damionsmith3295]

What if the data validation cell is initially empty? I tried this method and the individual formulas return T/F as necessary, but data validation returns no error. I didn't know there were excel gurus like you out there. Lets me know that I know nothing. I appreciate you videos

[@pradeepjangid490]

Great

[@yashsomaiya6491]

Hi Leila can you make a videos of ribbons. I would like to see videos on each and every tab in detail (one tab one video).

[@LeilaGharani]

I added it to my list. Thanks for your suggestion.

[@yashsomaiya6491]

@@LeilaGharani welcome

[@nurmuhammadnorman7392]

Thanks Leila. Love all your work! Is there a way to lock the range if the number had reach certain amount. For example I got a list, then after 100 the people can't put their name/tick/put 1. Thanks

[@ashishtrivedi8253]

You are best 👍 is it possible to give reference in formal of other sheets? It's not letting me.

[@brandoncrawford1036]

Is there a way to have it show a blank based off another cell and when that cell has a input it changes to the rest of the selection

[@rajsahu1029]

Nice

[@cyclingmc]

I like this but do you have a tutorial using VBA to check values entered into column matches validation list? Thank you for your support

[@mukundpotdar9849]

Its great lesson Leila, thank you!, But, I looking to do same thing using VBA.

[@HoiCCC]

You are beautiful and inteligent at the same. Thank you so much

[@AmitJha-bk5yd]

Nice video

[@pipo441]

Hi Leila, I used a data validation to proctite formula's in a row. Then I wanted to clear dat data (remove the validation). It did not work. It asked me to enter a formula.... weird because it did not allow me to go to the settings window in the first place. I did not save my work and started again.... Any idea?

[@lilchill6013]

Hello, nice tuts. Bought ur VBA tuts. Amazing. Can you do a tutorial on how to use a drop down to extract/filter out a set of Data

[@LeilaGharani]

Hi Lil. I'm glad you like the tutorials. Thank you for your support of my courses. Do you mean the filter functionality like this? kzbin.info/www/bejne/jKK0cnZ4lJtsibM . I cover copy and pasting of visible cells inside the course as well - specially when we cover the 2nd milestone of the course where we build a regional sales tool.

[@lilchill6013]

Leila Gharani yes just like that but instead of typing the criteria it is in the form of a drop down. See the first few seconds of this kzbin.info/www/bejne/pnSkiYWCjtdob6c Anyway I hope everyone buys your VBA course. It is THAT good. Can't wait for more!

[@kamrulzoha9223]

lil chill You can make dropdown of your creteria (It's simple )

[@holopanen]

Can this validation rules be applied on a table?

[@harleyborlee1339]

Great leason Leila, thank you! How can you prevent special characters using data validation?

[@dovids.greenberger435]

You can probably use the ASCII function to check if input is greater than or less than the numeric ASCII char value.

[@crysalbre]

I have a complex workbook that multiple people input data and the file is shared on SharePoint. I’ve noticed that the data validation settings do not function if they open the workbook in the browser but the validation will work if “open in app” is selected. I’ve encouraged everyone to open in the app but they’re not as a compliant as I’d like! Are there settings I am missing that will allow the data validation to work either way? Or, is there a setting that requires a workbook to be opened in the app rather than the browser? Thanks for you help!

[@kamrulzoha9223]

Thank you very much I was just stuck at one place And u slove that by "not'

[@LeilaGharani]

You're very welcome. Glad the "not" solved that.

[@eddiebarrett844]

I have a question. As you are checking for 'non-numeric' will it allow non-alpha numeric characters?

[@ImeldaLopez-rf7mr]

I just dont know how do you know which the numbers you have to use when doing a formula?

[@MrBumbum2812]

Hi Leila, is there any VBA code for 1 letter fixed & 4 number. The datavalidation will be replace if user copy&paste into cell

[@sandravukovic2901]

Thank you! :-)

[@xlnmq7132]

Hi leila your tutorials are really awesome and really useful but one question. how come i get an E instead of a 4 when i type =MID(A17,2, 1) ?

[@naserkarimi5348]

If we Sum data of multiple columns or rows that should be equal to 100 not less or more How to create this function? Thanks

[@Luciano_mp]

Show de bola, obrigado.

[@girishpoojari5134]

Thanks madam nice one

[@LeilaGharani]

You're very welcome.

[@fguimara]

Hi Leila, how are you? It wasn't exactly what I needed: is there any way to validate by selecting an entire column instead of just clicking and dragging desired cells? Thank you for any reply.