Ever get a really long nasty list of MAC addresses that aren’t in the aaaa.bbbb.cccc format that Cisco network devices crave?
Unfortunately, everyone seems to have their own idea of what a MAC address should look like. You will see on Windows systems, MAC addresses divided by dashes (00-50-56-a0-6e-db) other systems may use colons (c0:3f:d5:bd:22:64) etc.
I briefly worked at a company that needed constant VLAN membership changes (sometimes 50 or more endpoints at a time) I realized that simply typing MAC addresses to something a Cisco switch can understand would make up the majority of the time spent on the issue. Also, this left room for user mistakes and fat fingering errors.
I did some quick research and created an Excel workbook that takes the pain out of “improperly” formatted MAC addresses.
Click here to download the Excel Workbook (Cisco MAC Converter.xlsx)
Instructions:
- In the first column, paste in your nasty list of MAC addresses. You will need to do a “Find and Replace” (control F in Excel) to find any “-” or “:” and replace them with an empty character
- The second column will populate with the cleaned up MAC addresses using the friendly aaaa.bbbb.cccc format that Cisco devices like. Right click and copy all the cells in this column.
- In the 3rd column, right click and “Paste Special” and select “Values”.
I’d like to acknowledge Trevor G in this thread for posting up some Excel formula logic which I butchered up to make this all work. In case you were wondering about some of the underlying nuts and bolts, the B column basically applies this formula:
=SUBSTITUTE(A2,A2,LEFT(A2,4)&”.”)&MID(A2,5,4)&”.”&
Which inserts the dots on each address.
Hope this helps someone out there who needs it!
Leave A Comment