I want to require the length of a cell to be 14 numeric digits?
The user must enter all 14 digits (without a filler)

So the number could start with 0 but still must be 14 numeric digits:

ex)
00001200100123 - is valid
99999999999912 - is valid

123 - invalid

Goto to Data->Validation and set the "Allow:" option to "Text Length", set your minimum and maximum values to 14.
You may want to set the "Error Alert" message as well. The default error message isn't very helpful.

Dave.

I've tried this and it works somewhat. It allows for alphanumeric characters and I would perfer to limit it to numeric only. It also acts funny when I start with leading zeroes on some numbers.

Is there an alternative?

Thanks for your help!

This may be an Excel workaround for you. I am not aware of a way to force a data entry person to type 14 numeric characters directly into a cell.

Use data validation in the data entry cells by limiting entry to numbers between 1 and 99999999999999 so that no more than 14 characters may be entered.

The function below used in cells adjacent to the data entry cells will display the numbers with filled in leading zeroes so that the length will be 14 characters. You then would copy these reformatted numbers as values to where you want to use them, or you can copy them as values into the cells where they were entered (from column B to column A in my attached example). So for data entered in cell A1, the formula in B1 should be "=FrontFill(A1)" without the quotes.

Good luck.
Jerry

Function FrontFill(txt As String)

Application.Volatile
Dim ZLen As Integer, x As Integer

ZLen = Len(txt)

If ZLen > 0 Then
For x = 1 To (14 - ZLen)
txt = "0" & txt
Next x
End If
FrontFill = txt

End Function

I think that prior to doing this step, go to Format Cells and set up a Custom format with the fourteen digits, which will include leading zeroes, then the validation should work.

Custom formatting will display leading zeroes, but in my experience, the leading zeroes are not preserved when the cell value is copied to another cell, which my reformatting technique of using a function to add, in an adjacent column, the character "0" in front of the string until the length is 14, does preserve them.

If the goal is only to display 14-chararacter numbers in the cells where they are entered, then I would use your soultion of custom formatting combined with whole number data validation to limit entries of numeric values between 1 and 99999999999999. This data validation will not allow the entry of non-numeric characters.

Data validation suggested below by Dave, limiting the text length, will not prevent entering non-numeric characters.


Jerry