excel cell input validation

[DJ2018]

Hi All,

I' trying to set a cell so the user can only input a 11 digit number that can start with a zero and contain no spaces.
this is what I have thank for any assistance



this is set via the data validation
=AND(ISNUMBER(a1),LEN(a1)=11)

 

[mintjulep]

I' trying to set a cell so the user can only input a 11 digit number that can start with a zero and contain no spaces.

These two requirements are conflicting.

Other than the small subset of numbers 1>n>=0, no other number can start with a 0.

If you need padding zeros then you need to work with text that looks generally like numbers.

 

[Andrzejek]

Also, you need to be careful when using ISNUMBER() which evaluates if the expression can be evaluated to a number.

So this scientific expression is a number:
[tt]ISNUMBER(123e2)[/tt] is True, [tt]123e2[/tt] is a number.


---- Andy

There is a great need for a sarcasm font.

 

[DJ2018]

Thanks for the information guys but im lost now. Maybe my question should be:

Is possible to lock a cell down so a user can only input an 11 digit number sometimes including a zero at the beginning not allowing any special characters spaces just numbers ie: 01234567891 or 19876543211

Thanks

 

[Deniall]

I doubt you'll find a way to do exactly what you seem to be looking for.[ ] If this was my problem I would use VBA to write a user defined function (UDF) that did the checking and returned either TRUE or FALSE according to whether the cell contents passed or failed.[ ] I would then use this UDF to show the cell entry in red font if it failed.[ ] (Unfortunately - in Excel 2010 at least - you cannot use a UDF in data validation, only in conditional formatting.)

 

[DJ2018]

Thanks Deniall, back to the drawing board it is

 

[Deniall]

Your UDF needn't be complicated, just something along the following lines, but (as others have pointed out above) to accommodate those possible leading zeros you will have to format your cells as text.

Function ChkValidity(SerNumb) As Boolean
'
Dim I As Long, J As Long, Digit As String
'
J = Len(SerNumb)
If J <> 11 Then
    ChkValidity = False
    Exit Function
End If
'
For I = 1 To J
    Digit = Mid(SerNumb, I, 1)
    If Digit < "0" Or Digit > "9" Then
        ChkValidity = False
        Exit Function
    End If
Next I
'
ChkValidity = True
'
End Function

[Added later as an "edit"][ ] One minor, although perhaps VERY minor, advantage of using conditional formatting rather than data validation is that when the user makes a mistake (s)he does not need to acknowledge an error message before rectifying things.