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.



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


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


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




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



I hope this helps you!

1 Comment

Christian · July 14, 2020 at 1:38 pm

Awesome, thanks for this resource. Very helpful!

Leave a Reply