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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hello there, welcome to the board!

What type of values will you have in these cells?
 

brandoda22

New Member
Joined
Sep 7, 2007
Messages
11
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938

ADVERTISEMENT

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
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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]###-###-####
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Yes, yes. Sorry. I've been using 2007 as my default and should be running 2003 for my testings.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

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
Top