Custom number format for displaying MAC addresses?

L

Lakelands01

Board Regular

Joined

Jul 14, 2005

Messages

56

• Oct 18, 2005

• #2

If a column could be inserted, this formula may work:

=MID(A1,1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&MID(A1,11,2)

This formats the value in cell A1 which is: ae12e034bb00 to ae:12:e0:34:bb:00

 

Upvote 0

Tazguy37

MrExcel MVP

Joined

May 28, 2004

Messages

4,237

• Oct 18, 2005

• #3

If you can't use an extra column, a VBA solution like this might work for you:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText As String

    If Len(Target) <> 12 Then Exit Sub  'check for correct length
    If Target.Column <> 1 Then Exit Sub 'only Column A
    Application.EnableEvents = False

    myText = Left(Target.Text, 2) _
        & ":" & Mid(Target.Text, 3, 2) _
        & ":" & Mid(Target.Text, 3, 2) _
        & ":" & Mid(Target.Text, 3, 2) _
        & ":" & Mid(Target.Text, 3, 2) _
        & ":" & Right(Target.Text, 2)
    Target.Value = myText               'put MAC string back into original cell

    Application.EnableEvents = True
End Sub

• How to use the above code:
  
  Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer.
  Click "Microsoft Excel Objects" for the file you're working on.
  Double-click the sheet module where you need this to work.
  
  Open the Code pane with F7.
  Paste the above code in.
  Press Alt-Q to close the VBE and return to Excel

Hope that helps!

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Oct 18, 2005

• #4

For a similar thread, see
http://www.mrexcel.com/board2/viewtopic.php?t=36822&highlight=

lenze

 

Upvote 0

S

skiraly017

New Member

Joined

Oct 18, 2005

Messages

6

• Oct 19, 2005

• #5

Thanks for the help...

...but I couldn't get either of those methods to work. Maybe I'm trying to do something that can't be done.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 19, 2005

• #6

How did you try the various suggestions?

Could you post some sample data?

You've indicated the result you want but not shown us the raw data.

 

Upvote 0

S

skiraly017

New Member

Joined

Oct 18, 2005

Messages

6

• Oct 19, 2005

• #7

The results.

I followed the VBA idea instructions to the letter, but when I entered the "unformatted" MAC address and hit enter, it stayed unformatted.

The formula generated a looping(?) error once and then form that point on just placed itself as the test in whatever cell I tried to apply it to.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 19, 2005

• #8

Where did you enter the value to be formatted?

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Oct 19, 2005

• #9

Assumming your addresses are in column A, starting in row 2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'column of interest
If Target.Row < 2 Then Exit Sub 'lowest row
If Target.Cells.Count > 1 Then Exit Sub 'no multiple selections
With Target
'ex: ae:12:e0:34:bb:00
Application.EnableEvents = False
.Value = Left(.Value, 2) & ":" & Mid(.Value, 3, 2) & ":" & Mid(.Value, 5,2) & ":" & Mid(.Value,7,2)& ":" & Right(.Value,2)
Application.EnableEvents = True
End With
End Sub

This goes in the WorkSheet module

HTH

lenze

 

Upvote 0

S

skiraly017

New Member

Joined

Oct 18, 2005

Messages

6

• Oct 19, 2005

• #10

Lenze hit the nail on the head.

I had to modify the script because it was only allowing five octets and dropping the sixth.

How would I modify the script so that the format would apply to all columns, not just "A"?

TIA.

 

Upvote 0