Can I force MS Excel table column to use the specified pattern?

Question

I need to develop a tool that enforces strict format of some table. The fields in this table follow specified patterns like number:number (11:20. 2:00), agreed date (11/20/2012 but not locale-dependent), a single integer, a single floating point value and so on.

The table should reject wrong values or highlight them in red. Apart that, the user must be capable of freely navigating and editing the table (allowing to add data just through some form in popup dialog looks like an awful solution).

Before starting to write a custom application, I would like to ask, how difficult is to implement this requirement in MS Excel, using document templates? I did some googling but without direct hit.

Using some other tool that easily supports this feature would also be acceptable.

Extension: here seems also an example how to use regular expressions.

Answer 1

You can achieve this easily using the Data Validation feature for the input cell:

Select the cell(s) you want to enforce the format and go to the Data tab -> Data Tools->Data Validation. Here you can set the restrictions as well as error message and hints. If the restriction is more complex than the default options, you can build custom validation formulas. E.g. if you want cell A1 to only start with "Test", select A1 and enter =LEFT(A1,4)="Test" as custom formula. As long as the formula returns TRUE, the input is accepted, else it's rejected.

In addition, make sure to format the cells according to the format you want it (e.g. the data). This way, even if the user input 01/01/13, it can be displayed as 01/01/2013 and the validation passes.

Last but not least make sure to protect the worksheet: Uncheck the "Locked" property (in the format cells dialog) of all input cells and then protect the worksheet (right click on the worksheet name).