Custom Number Formatting Troubles

[jgodfrey]

I am trying to set a custom number format for some cells to
automatically add a hyphen between every two sets of numbers. I am
trying to be able to punch in the MAC address of a piece of hardware
and have it automatically input the hyphens in the address for me.

For example, an address like this: 0A-00-3E-F0-12-34 or like this
0A-00-3E-F0-1B-4C.

-----

I have tried the following code: ##-##-##-##-##-## and it works fine
until you enter in the alphabetic characters. So, I modified my code
to this: 0\A-00-3\E-F0-##-## and it will work ok except in the case of
an alphabetic character in the last four digits then it does not work
at all. I want to be able to have it work for all 12 manually entered
characters and then have it insert the hyphens between the pairs.

Any help that you could provide to me in regards to this dilemma would
be greatly appreciated. Thank you for your help and time with this
matter.



------------------------------------------------




------------------------------------------------

 

[Dave Peterson]

Number formats work with numbers--not text.

But you could have a macro working in the background helping you that actually
inserts the extra dashes.

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

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myString As String

If Intersect(Target, Range("a2:a9999")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

myString = Target.Value

If Len(myString) - Len(Application.Substitute(myString, "-", "")) = 5 Then
Exit Sub 'already done??
End If

myString = Right(String(12, "0") & myString, 12)

Application.EnableEvents = False
Target.Value = Mid(myString, 1, 2) & "-" & _
Mid(myString, 3, 2) & "-" & _
Mid(myString, 5, 2) & "-" & _
Mid(myString, 7, 2) & "-" & _
Mid(myString, 9, 2) & "-" & _
Mid(myString, 11, 2)

errHandler:
Application.EnableEvents = True

End Sub



I limited my range to A2:A9999. Change this to match your data. If you type
something with less than 12 characters, I pad at the left with zeros.