I was at a client recently and they wanted to limit data input in an Excel column to have a number starting with 4 and then five numbers after it.
I have worked out a way of doing this using Data Validation.
First, select the data range to which the data validation rule is to be applied and format it as text – Home tab – Number group and from the dropdown where it says General select Text.
Then with the data range still selected, go to the Data tab, then in the Data Tools group, select Data Validation. On the Settings tab, from the Allow dropdown, select Custom.
In the Formula box, the formula in this case is =COUNTIF(C1, “4?????), where C1 is the first cell in the selected range and I want the number to be 4 followed by 5 other numbers.
Although it will be formatted as text, if these are part numbers they are never going to have calculations done on them so works well for data input.