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!