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.
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc).
I understand I could type each octet into a different cell, and then use a formula to combine them into one cell. But that would defeat the object of what I want to do. I want to type a string of numbers, i.e. 001AC32BBA22 by using number pad and keyboard. Then have Excel post the number in one cell as 00:1A:C3:2B:BA:22. Converting the numbers to decimal is not an option, I don't want decimal, I want the mac in hex actual.
![Format Format](/uploads/1/2/5/3/125387359/278000726.png)
Thank you for your help and assistance. Maybe my question was not phrased correctly. I want to enter a string of alpha/numeric text characters, and have Excel automatically separate the string into groups of two text characters, separated by a colon (:). Dave Peterson 16/4/2008, 17:18 น. If you have at least one alpha character in your MAC address (I gave you formulas for IP addresses-sorry), you could use another formula to split them.
![Excel Excel](https://d2d42mpnbqmzj3.cloudfront.net/images/stories/doc-excel/doc-mac-format/xdoc-mac-format-3.png.pagespeed.ic.on5Ekur7u_.png)
(If you have a MAC address that is all numeric, make sure you enter it as text by preceding the entry with an apostrophe or preformatting the cell as Text.) Then in an adjacent cell: =left(a1,2)&':'&mid(a1,3,2)&':'&mid(a1,5,2)&':'&mid(a1,7,2)&':'&mid(a1,9,2) You could actually have an event macro that does this parsing, but I'd use the formula. Dave Peterson Barb Reinhardt 16/4/2008, 18:57 น. I'm not sure you can format a cell to do this, but you can do it with a Worksheet change event. Right click on the sheet tab and view code. Paste this in the sheet module.
You may need to modify so that it doesn't parse every cell with 12 characters. Private Sub WorksheetChange(ByVal Target As Range) Application.EnableEvents = False If Len(Target.Text) = 12 Then For i = 1 To 11 Step 2 If mystring = ' Then mystring = Mid(Target, i, 2) Else mystring = mystring & ':' & Mid(Target, i, 2) End If Next i Target = mystring End If Application.EnableEvents = True End Sub - HTH, Barb Reinhardt.