MAC address format

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"