Data Validation with Text, 3 digits maximum. Problems with leading zeros.

In cell D5, I want to capture an entry of 3 digits max. If the number entered is less than 100, leading zeros should automatically return. So if the user entered 3 it would show as 003. If they entered 30, it would show as 030.

In D5, I also have Data Validation as Text Length with a max of 3 digits.

In cell D6 I want to capture the value in D5, i.e. 003, 030, 300, etc.




In cell D5 I have set up a cell with Data Validation using Text Length for the Validation Criteria. It has been limited to "Less than or Equal to" = 3 so I can restrict entries to 3 digits.

In cell D6 I am referencing the result in D5 with TEXT($D$5,"000").

Here is the problem. If I enter 0003 I should get an error message because I have entered more than 3 characters. But the leading zero gets dropped off and the result in the cell is 003. I want it to flag this as an error because it is a 4 digit entry . One way to correct this is to change the cell format to Text. But if I do that and enter 3 in D5, it shows as 3 instead of 003 which I don't want.

It's as if Data Validation ignores leading zeros and cuts things down to the 3 digit maximum.

So one fix results in another problem.

Any ideas on how to get Excel to recognize 0003 as a four digit entry?