Exceljet

Quick, clean, and to the point

MAC address format

Excel formula: MAC address format
Generic formula 
=TEXTJOIN(A1,1,MID(separator,SEQUENCE(6,1,1,2),2))
Summary 

To format a MAC address string without delimiters to a MAC address separated by a colon (:) or hyphen (-), you can use a formula based on the TEXTJOIN, MID, and SEQUENCE functions. In the example shown, the formula in D5, copied down, is:

=TEXTJOIN(C5,1,MID(B5,SEQUENCE(6,1,1,2),2))

The formula returns the formatted strings as seen in column D.

Explanation 

A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen (-) like this:

"01-23-45-67-89-ab"
"01:23:45:67:89:ab"

To format a text string with 12 characters in the same way, you can use a formula like this:

=TEXTJOIN(C5,1,MID(B5,SEQUENCE(6,1,1,2),2))

Working from the inside out, the SEQUENCE function is used to generate an array of 6 numbers used as the start_num argument in the MID function:

SEQUENCE(6,1,1,2) // returns {1;3;5;7;9;11}

These are returned directly to the MID function:

MID(B5,{1;3;5;7;9;11},2)

With the text "112233445566" in B5, the MID function returns an array of 6 strings:

{"11";"22";"33";"44";"55";"66"}

This array is returned to the TEXTJOIN function as the text1 argument, and with the colon (:) as the delimiter from C5, we have:

=TEXTJOIN(";",1,{"11";"22";"33";"44";"55";"66"})

The TEXTJOIN function concatenates the 6 strings together using a colon, and returns a single string as a final result:

11:22:33:44:55:66

The formula in D6 works exactly the same, except it uses the hyphen in C6 to join the strings: 

11-22-33-44-55-66

Three groups of four

Another standard format is 3 groups of 4 hexadecimal digits, separated with a dot. To create a MAC address in this format, use a formula like this:

=TEXTJOIN(".",1,MID(B5,SEQUENCE(3,1,1,4),4))

SEQUENCE now generates 3 start numbers incremented by 4 characters:

SEQUENCE(3,1,1,4) // returns {1;5;9}

And MID returns 3 strings:

{"1122";"3344";"5566"}

The TEXTJOIN function then concatenates these strings separated with a dot (.) character:

"1122.3344.5566"
Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.