Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Limit text length in Excel for ID and passport numbers

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.

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.
Limit text length in Excel for ID and passport numbers

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!)

Limit text length in Excel for ID and passport numbers

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.

Copy Data Validation to cells that have data in them

Combine text in Excel