Allow Numeric Characters Only In A Cell

Hello!

I am wanting to both limit a cell to numeric entry only and require 12-digits.
Example of typical entry: 01-2005-12345.
I have to ensure that the zero will show up at the start if that is what
they need to enter and the dashes must fall in the string as shown. I do not
want the user to have to key the dashes.
I currently have been able to use "Format Cells" to ensure the zero shows up
and the dashes automatically insert into the string by using custom format
and 0#-####-##### , but I am not able to require 12-digits and limit to
numeric entry only.

Any help would be great!

Thanks, Mel

You can use data validation to restrict the entry to a number.
Set a minimum and maximum number based on what you'll allow in the cell.

However, you say you require 12 digits, but only show 11 digits in your
custom format, so it's not clear what the allowed range should be.

Mel wrote:
> Hello!
>
> I am wanting to both limit a cell to numeric entry only and require 12-digits.
> Example of typical entry: 01-2005-12345.
> I have to ensure that the zero will show up at the start if that is what
> they need to enter and the dashes must fall in the string as shown. I do not
> want the user to have to key the dashes.
> I currently have been able to use "Format Cells" to ensure the zero shows up
> and the dashes automatically insert into the string by using custom format
> and 0#-####-##### , but I am not able to require 12-digits and limit to
> numeric entry only.
>
> Any help would be great!
>
> Thanks, Mel


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Yes, the "12" was a typo. The field length is 11.
I used validation to set the length to 11 and used the "whole number"
selection to allow only numeric.
My only problem now is that the first 2 digits are often 01, 02, etc.
If I enter this string.................02-2005-12345, everything works
perfectly
however......
If a I enter this string...............02-2005-123456
The result in the field is.......... 22-0051-23456
I just can not get it to leave the zero after I hit "enter".

Thank you!!



"Debra Dalgleish" wrote:

> You can use data validation to restrict the entry to a number.
> Set a minimum and maximum number based on what you'll allow in the cell.
>
> However, you say you require 12 digits, but only show 11 digits in your
> custom format, so it's not clear what the allowed range should be.
>
> Mel wrote:
> > Hello!
> >
> > I am wanting to both limit a cell to numeric entry only and require 12-digits.
> > Example of typical entry: 01-2005-12345.
> > I have to ensure that the zero will show up at the start if that is what
> > they need to enter and the dashes must fall in the string as shown. I do not
> > want the user to have to key the dashes.
> > I currently have been able to use "Format Cells" to ensure the zero shows up
> > and the dashes automatically insert into the string by using custom format
> > and 0#-####-##### , but I am not able to require 12-digits and limit to
> > numeric entry only.
> >
> > Any help would be great!
> >
> > Thanks, Mel
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Without considering Data|Validation, you said you used a custom format of:

0#-####-#####

How should you enter something to get:

02-2005-123456

That last piece has 6 digits in it.

if you typed
02200512345

then your custom format would work fine.



Mel wrote:
>
> Debra,
>
> Yes, the "12" was a typo. The field length is 11.
> I used validation to set the length to 11 and used the "whole number"
> selection to allow only numeric.
> My only problem now is that the first 2 digits are often 01, 02, etc.
> If I enter this string.................02-2005-12345, everything works
> perfectly
> however......
> If a I enter this string...............02-2005-123456
> The result in the field is.......... 22-0051-23456
> I just can not get it to leave the zero after I hit "enter".
>
> Thank you!!
>
> "Debra Dalgleish" wrote:
>
> > You can use data validation to restrict the entry to a number.
> > Set a minimum and maximum number based on what you'll allow in the cell.
> >
> > However, you say you require 12 digits, but only show 11 digits in your
> > custom format, so it's not clear what the allowed range should be.
> >
> > Mel wrote:
> > > Hello!
> > >
> > > I am wanting to both limit a cell to numeric entry only and require 12-digits.
> > > Example of typical entry: 01-2005-12345.
> > > I have to ensure that the zero will show up at the start if that is what
> > > they need to enter and the dashes must fall in the string as shown. I do not
> > > want the user to have to key the dashes.
> > > I currently have been able to use "Format Cells" to ensure the zero shows up
> > > and the dashes automatically insert into the string by using custom format
> > > and 0#-####-##### , but I am not able to require 12-digits and limit to
> > > numeric entry only.
> > >
> > > Any help would be great!
> > >
> > > Thanks, Mel
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
> >

--

Dave Peterson

Yes, when I type 02200512345 everything does work fine. However, If I happen
to type 022005123456 (inadvertently adding the 6) the result ends up with the
22005123456 and I lose the "0" at the beginning, therefore an incorrect value.
This sheet is to be distributed to many users and when they send the
document in the field is read automatically and indexed into another system.
As much as we can not control a user entering in a wrong number, we are
trying to make ithe process as "mistake-possible" as we can.
The problems now is just when we have a leading zero AND if a user
inadvertently adds an extra digit.

"Dave Peterson" wrote:

> Without considering Data|Validation, you said you used a custom format of:
>
> 0#-####-#####
>
> How should you enter something to get:
>
> 02-2005-123456
>
> That last piece has 6 digits in it.
>
> if you typed
> 02200512345
>
> then your custom format would work fine.
>
>
>
> Mel wrote:
> >
> > Debra,
> >
> > Yes, the "12" was a typo. The field length is 11.
> > I used validation to set the length to 11 and used the "whole number"
> > selection to allow only numeric.
> > My only problem now is that the first 2 digits are often 01, 02, etc.
> > If I enter this string.................02-2005-12345, everything works
> > perfectly
> > however......
> > If a I enter this string...............02-2005-123456
> > The result in the field is.......... 22-0051-23456
> > I just can not get it to leave the zero after I hit "enter".
> >
> > Thank you!!
> >
> > "Debra Dalgleish" wrote:
> >
> > > You can use data validation to restrict the entry to a number.
> > > Set a minimum and maximum number based on what you'll allow in the cell.
> > >
> > > However, you say you require 12 digits, but only show 11 digits in your
> > > custom format, so it's not clear what the allowed range should be.
> > >
> > > Mel wrote:
> > > > Hello!
> > > >
> > > > I am wanting to both limit a cell to numeric entry only and require 12-digits.
> > > > Example of typical entry: 01-2005-12345.
> > > > I have to ensure that the zero will show up at the start if that is what
> > > > they need to enter and the dashes must fall in the string as shown. I do not
> > > > want the user to have to key the dashes.
> > > > I currently have been able to use "Format Cells" to ensure the zero shows up
> > > > and the dashes automatically insert into the string by using custom format
> > > > and 0#-####-##### , but I am not able to require 12-digits and limit to
> > > > numeric entry only.
> > > >
> > > > Any help would be great!
> > > >
> > > > Thanks, Mel
> > >
> > >
> > > --
> > > Debra Dalgleish
> > > Excel FAQ, Tips & Book List
> > > http://www.contextures.com/tiptech.html
> > >
> > >
>
> --
>
> Dave Peterson
>

I'm not sure you can do this with Data|Validation and custom formatting.

If you type:
0000000000012
Excel will still just see it as 12. It doesn't know that you meant something
else.

One alternative is to format the cell as text and let a worksheet event handle
the validity checks and formatting for you.

If you think you like this idea, right click on the worksheet tab that should
have this behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

Set myRng = Me.Range("a:a")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False

For Each myCell In Intersect(Target, myRng).Cells
With myCell
If IsEmpty(.Value) Then
'do nothing
ElseIf Application.IsNumber(.Value) Then
MsgBox "Please enter Text in: " & .Address(0, 0)
.ClearContents
ElseIf Len(.Value) > 11 Then
MsgBox "No more than 11 characters in: " & .Address(0, 0)
.ClearContents
Else
For iCtr = 1 To Len(.Value)
If IsNumeric(Mid(.Value, iCtr, 1)) Then
'keep looking
Else
MsgBox "Only Numeric in: " & .Address(0, 0)
.ClearContents
Exit For
End If
Next iCtr

.Value = Format(.Value, "00-0000-00000")
End If
End With
Next myCell

errHandler:
Application.EnableEvents = True

End Sub





If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

===
One word of warning. Like most macros in excel, this will break the Edit|undo
feature.





Mel wrote:
>
> Yes, when I type 02200512345 everything does work fine. However, If I happen
> to type 022005123456 (inadvertently adding the 6) the result ends up with the
> 22005123456 and I lose the "0" at the beginning, therefore an incorrect value.
> This sheet is to be distributed to many users and when they send the
> document in the field is read automatically and indexed into another system.
> As much as we can not control a user entering in a wrong number, we are
> trying to make ithe process as "mistake-possible" as we can.
> The problems now is just when we have a leading zero AND if a user
> inadvertently adds an extra digit.
>
> "Dave Peterson" wrote:
>
> > Without considering Data|Validation, you said you used a custom format of:
> >
> > 0#-####-#####
> >
> > How should you enter something to get:
> >
> > 02-2005-123456
> >
> > That last piece has 6 digits in it.
> >
> > if you typed
> > 02200512345
> >
> > then your custom format would work fine.
> >
> >
> >
> > Mel wrote:
> > >
> > > Debra,
> > >
> > > Yes, the "12" was a typo. The field length is 11.
> > > I used validation to set the length to 11 and used the "whole number"
> > > selection to allow only numeric.
> > > My only problem now is that the first 2 digits are often 01, 02, etc.
> > > If I enter this string.................02-2005-12345, everything works
> > > perfectly
> > > however......
> > > If a I enter this string...............02-2005-123456
> > > The result in the field is.......... 22-0051-23456
> > > I just can not get it to leave the zero after I hit "enter".
> > >
> > > Thank you!!
> > >
> > > "Debra Dalgleish" wrote:
> > >
> > > > You can use data validation to restrict the entry to a number.
> > > > Set a minimum and maximum number based on what you'll allow in the cell.
> > > >
> > > > However, you say you require 12 digits, but only show 11 digits in your
> > > > custom format, so it's not clear what the allowed range should be.
> > > >
> > > > Mel wrote:
> > > > > Hello!
> > > > >
> > > > > I am wanting to both limit a cell to numeric entry only and require 12-digits.
> > > > > Example of typical entry: 01-2005-12345.
> > > > > I have to ensure that the zero will show up at the start if that is what
> > > > > they need to enter and the dashes must fall in the string as shown. I do not
> > > > > want the user to have to key the dashes.
> > > > > I currently have been able to use "Format Cells" to ensure the zero shows up
> > > > > and the dashes automatically insert into the string by using custom format
> > > > > and 0#-####-##### , but I am not able to require 12-digits and limit to
> > > > > numeric entry only.
> > > > >
> > > > > Any help would be great!
> > > > >
> > > > > Thanks, Mel
> > > >
> > > >
> > > > --
> > > > Debra Dalgleish
> > > > Excel FAQ, Tips & Book List
> > > > http://www.contextures.com/tiptech.html
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson