Cisco MAC Address Formatter: Because Cisco has to be special.

This content is licensed under The MIT License. See here for more details.

IOS formats MAC addresses as 0000.dead.beef. Sometimes I think it’s just to annoy me, but there’s probably an actual reason. Nonetheless, it makes pattern matching more difficult in Excel. So here’s a formula to convert to the more common 00:00:de:ad:be:ef form.

=LET(
    mac,
    TRIM(<MacAddress>),
    CONCAT(
        LEFT(mac, 2),
        ":",
        MID(mac, 3, 2),
        ":",
        MID(mac, 6, 2),
        ":",
        MID(mac, 8, 2),
        ":",
        MID(mac, 11, 2),
        ":",
        RIGHT(mac, 2)
    )
)

This will work fine without the LET function (< Excel 2021), but you’ll need to trim the value for every substring.