Ever get a really long nasty list of MAC addresses that aren’t in the aaaa.bbbb.cccc format that Cisco network devices crave?

Typical Windows command prompt arp -a output

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!