Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: require length of cell

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    8
    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    11
    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!

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Cool Limit Data Entry to 14 Numeric Characters

    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
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by JerryDal
    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.
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •