Using data validation to control text length

By Neil Shorney, Director, Naturally Sales Ltd.

In the past e-tip, we’ve looked at how to indicate when data is too long by using an IF statement, then how to cut data down to the required size using the LEFT function. In this week’s tip, we’ll see how we can prevent over-long data being entered in the first place. Prevention is better than a cure, after all!

First, select the cell (or cells) which you'd like to restrict. Then click the Data Validation button. If you're using Excel 2007 or above, you'll find the Data Validation button on the Data tab, in the middle of the Data Tools group. In Excel 2003, it's on the Data menu then Validation. Click this button and you'll be presented with the Data Validation dialog.

Data validation allows us to restrict the values a user can enter into our spreadsheets. The default is "Any value". Change this to "Text length", and choose the minimum and maximum length you'll allow. There are other operators to choose in the "Data" drop-down.

On the "Input Message" tab, you can type a brief message to tell your user what you're expecting, and on the "Error Alert" tab, you can type a message in case your user insists on trying to do things their own way!