Format MAC addresses: standardize colons (00:1A:2B...) or Cisco dots

Normalize DHCP, switch, and asset-inventory MAC formats

Network exports represent the same hardware address as "001a2b3c4d5e", "00-1A-2B-3C-4D-5E", or "001a.2b3c.4d5e"—causing VLOOKUP and UNIQUE reconciliations to fail.

Stripping "-", ":", ".", and spaces inside LET isolates the 12 raw hex characters so MID slices can reassemble colon-separated format (XX:XX:XX:XX:XX:XX) or Cisco lowercase quad format (xxxx.xxxx.xxxx).

colon-separated and Cisco quad MAC formulas

Validating LEN(m) <> 12 catches truncated paste errors immediately.

=LET(m, UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),":",""),".","")," ","")), IF(OR(LEN(m)<>12, LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(m),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""))<>0), "Invalid MAC", MID(m,1,2)&":"&MID(m,3,2)&":"&MID(m,5,2)&":"&MID(m,7,2)&":"&MID(m,9,2)&":"&MID(m,11,2)))

Cisco 4-hex dot notation

Grouping 4 lowercase hex digits with "." formats switch CLI commands cleanly.

=LET(m, LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),":",""),".","")," ","")), IF(OR(LEN(m)<>12, LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(m),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""))<>0), "Invalid MAC", MID(m,1,4)&"."&MID(m,5,4)&"."&MID(m,9,4)))

Inputs and interpretation

The formula validates twelve hexadecimal characters after separator removal. It formats 48-bit addresses only; it does not validate ownership, uniqueness, multicast/local bits, or EUI-64 addresses.

Further reading

• Microsoft Support — MID function