You can use Data Validation to stop typo’s when people are entering information. So perhaps you want to limit text length in Excel for ID and passport numbers. If it is an ID number then exactly 13 characters must be entered, but if it is another document type then you can allow more or less.
Table of contents
YouTube Limit text length in Excel
Create an text length limitation based on a selection
As shown below in column A we have a dropdown that says whether the document is an ID or other document. This column drives column C and D which (using an IF function) creates the minimum and maximum number of characters allowed for that type.
Then we just need to highlight the relevant cells in column B, and
- Click DATA and DATA VALIDATION
- Choose to allow Text Length
- Choose Between
- in the Minimum section link it to cell C8
- in the Maximum section link it to cell D8
- Press OK.
Now when it says ID in column A (e.g. cell A10), then if we enter the wrong number in B10 it will stop the user with an error message. So below we entered a passport number in B10 when it should have been an ID number of 13 characters. We get a custom error message (you can do this in Data Validation as well!)
This is a good way to stop typos at the source instead of fixing them only later when they are found.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.