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

Use custom data validation formulas in Excel to allow only valid codes like C2345 or PT4534. 📖 Read the full guide with all formulas: https://www.xelplus.com/excel-custom-data-validation/?utm_source=yt-video&utm_medium=vid-description&utm_campaign=excel-custom-data-validation&utm_content=custom-data-validation 🎓 Excel Essentials for the Real World: https://www.xelplus.com/course/learn-excel-essentials/?utm_source=yt-video&utm_medium=vid-description&utm_campaign=essentials&utm_content=custom-data-validation (500,000+ enrolled in our courses) I solve three cases: one letter followed by four digits, a fixed "PT" followed by four digits, and any two letters followed by four digits. I build each check in a cell first so Excel helps with the arguments: LEN for the length, RIGHT with ISNUMBER and VALUE for the digits, LEFT and MID with NOT for the letters. Then I combine them with AND and paste the formula into Data Validation, Custom. ✅ Good to know: Data validation only checks what people type. Values pasted into the cell skip the rule. NOT(ISNUMBER(...)) checks "not a digit", so a symbol like # also passes. Add a check if that matters. In Microsoft 365 you can do case 1 in one formula: =REGEXTEST(A5,"^[A-Za-z]\d{4}$") ⏱️ Chapters: 00:00 Custom data validation for text and number codes 00:40 Case 1: one letter followed by 4 digits 00:59 Where to find Custom validation 01:12 Write the formula in a cell first 01:42 Check the length with LEN 02:47 Check the last 4 are digits: RIGHT, ISNUMBER, VALUE 04:29 Check the first character isn't a number: LEFT and NOT 05:50 Combine the checks with AND 06:05 Use the Clipboard to combine formulas 06:47 Paste the formula into Data Validation 07:22 Copy the validation to other cells 07:58 Case 2: starts with PT plus 4 digits 09:48 Case 3: any 2 letters plus 4 digits 10:50 Why checking two letters at once fails 11:14 Check the second character with MID 13:05 Combine and test the final rule 🎬 Related videos: 🎬 Data validation basics: https://youtu.be/FRiFfKb_B_A 🎬 New REGEX functions in Excel: https://youtu.be/YFnXV2be9eg ➡️ Join this channel: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join 👉 Get one practical Excel tip each week in our newsletter: https://link.xelplus.com/yt-d-newsletter 🚩 Connect with us: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/in/leilagharani #excel #datavalidation #exceltips