Convert list of unformatted MAC addresses to proper format

Today I had to decommission a Windows Server which was running a DHCP role.

The client has a Meraki firewall and on the firewall is an option to import all the IP reservations from a formatted CSV:

 

 

“That’s great!”, I thought.

I can get all ~100 reserved IPs in at once as long as I format the exported table that same way!

 

It’s too bad that the windows server “export” option for the DHCP role left me with this mess for MAC format:

 

So naturally I looked for a way to quickly change this format into one that the Meraki dashboard would accept.

 

First

open Excel and make sure the MACs are all in a column.

Next

open the Visual Basic editor in Excel (ALT+F11) and go to Insert > New Module

Then

Paste this into the window that comes up:

 

Sub ConvertToMAC()
Dim c As Range
For Each c In Selection
    c.Value = Left(c, 2) & ":" & Mid(c, 3, 2) & ":" & Mid(c, 5, 2) & ":" & Mid(c, 7, 2) & ":" & Mid(c, 9, 2) & ":" & Right(c, 2)
Next c
End Sub

 

 

Now

hit ALT+Q to quit the editor and proceed to highlight the fields containing the MAC addresses to format.

 

Run the macro

with ALT+F8 or navigate to the proper location

 

Viola!

I hope this helps you!