MAC address formatting

  • Thread starter Thread starter Daniel Watkins
  • Start date Start date
D

Daniel Watkins

Thanks in advance to anyone who can help me whit this.

I need a cell to carry out the following formatting. A user types in a
string of 12 numbers and letters (MAC Address) e.g. tb3c9ffd1ae5 and
the cell formats the information into the below format:

TB-3C-9F-FD-1A-E5

or this format if it is easier:

TB:3C:9F:FD:1A:E5

The cell also needs to check that the number of numbers and letters does
not exceed 12 in total.

Dan
 
Right click on the sheet tab and paste in code like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo ErrHandler
Application.EnableEvents = False
sStr = Target.Value
If Len(sStr) <> 12 Then
MsgBox "Enter 12 characters"
Target.ClearContents
Else
sStr1 = ""
For i = 1 To 5
sStr1 = sStr1 & Mid(sStr, (i - 1) * 2 + 1, 2) & "-"
Next
sStr1 = sStr1 & Right(sStr, 2)
Target.Value = sStr1
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

change column = 3 to indicate the column where you want this behavior.

If the behavior happens after a certain row change to

If Target.Column = 3 and Target.Row > 2 Then
 
Back
Top