MAC Address conversion using Excel
I have been working with few different hardware devices that store MAC address information in different formats.
This prevents me from quickly searching for MAC address entries through logs, configuration etc.
Here is a simple Excel formula that helps me to convert the MAC addresses from the standard colon delimited values (:), to the dot (.) delimited values used by most networking equipment such as Cisco Switches and Routers.
=LEFT(A1,2)&MID(A1,4,2)&”.”&MID(A1,7,2)&MID(A1,10,2)&”.”&MID(A1,13,2)&RIGHT(A1,2)
The above formula works so that the mac address residing in cell A1 in format 00:51:57:76:ba:fc is converted to 0051.5776.bafc
The above formula will also work for the dash (-) separated MAC addresses.
Hope I get back to this article when I need it next time instead of reinventing the wheel.