Formatting a MAC address

I’m often given huge lists of MAC addresses and they tend to arrive in a variety of different formats. Such as the following:

AABBCCDDEEFF
AA-BB-CC-DD-EE-FF
AAB.BCC.DDE.EFF
AABB.CCDD.EEFF
AA:BB:CC:DD:EE:FF

Typically I need them formatted as the last example above, with colons between every 2 characters.

The formula below works great in Excel, where A2 is the cell you’re targeting:

=LEFT(A2,2)&”:”&MID(A2,3,2)&”:”&MID(A2,5,2)&”:”&MID(A2,7,2)&”:”&MID(A2,9,2)&”:”&RIGHT(A2,2)

Leave a Reply

Your email address will not be published. Required fields are marked *