How do I get the dashes to not count as a digit/number?

brandoda22

New Member
Joined
Sep 7, 2007
Messages
11
I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Target) > 11) Or (Len(Target) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
If Len(Target) = 10 Then Target.Value = Format(Target, "###-###-####")
If Len(Target) = 11 Then Target.Value = Format(Target, "##-###-##-####")
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hello there, welcome to the board!

What type of values will you have in these cells?
 
Upvote 0
we are going to be putting 10 or 11 digit account numbers in the specific cells, with the specific formating for each that is specified in the script.
 
Upvote 0
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Len(Target) > 11 Or Len(Target) < 10 Then
    MsgBox "Not a valid number of characters, please try again"
    Target.Value = vbNullString
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
End If
If Not IsNumeric(Target.Value) Then
    MsgBox "Enter only numbers (no dashes), please try again"
    Target.Value = vbNullString
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
End If
If Len(Target) = 10 Then Target.Value = Format(Target, "###-###-####")
If Len(Target) = 11 Then Target.Value = Format(Target, "##-###-##-####")
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
This should work. It uses the worksheet function SUBSTITUTE to remove the dashes and count the length of the resulting string.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Application.WorksheetFunction.Substitute(Target, "-", "")) > 11) Or (Len(Application.WorksheetFunction.Substitute(Target, "-", "")) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
targetword = Application.WorksheetFunction.Substitute(Target, "-", "")
If Len(Application.WorksheetFunction.Substitute(Target, "-", "")) = 10 Then Target.Value = Format(targetword, "###-###-####")
If Len(Application.WorksheetFunction.Substitute(Target, "-", "")) = 11 Then Target.Value = Format(targetword, "##-###-##-####")
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Why even use code then?

Select all cells (I'll assume A1 is going to be the active cell)
Press Ctrl + 1 | Number (tab) | Custom
###-###-####
Ok
Now conditionally format all selected cells to use a formula of ...
=LEN(A1)=11
Change the format to
##-###-##-####

That's it.

HTH
 
Upvote 0
firefytr:

Is that an XL2007 thing, in XL2003 or XL2002, you can't set a number format in Conditional Formatting.

brandoda22:

How about a custom number format of:

[>=10000000000]###-###-##-####;[<10000000000]###-###-####
 
Upvote 0
Yes, yes. Sorry. I've been using 2007 as my default and should be running 2003 for my testings.
 
Upvote 0
Hi brandoda22:

In line with HOTPEPPER's thinking, how about the following Custom Number Format ...
Code:
[>99999999999]"NG";[>9999999999]00-000-00-0000;000-000-0000;
It will format 11 and 10 digit numbers per your spec;
for a 12 digit entry it will display NG
for a less than 10 digit entry, it will pad 0's to make up a 10 digit entry and then display it accordingly.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top