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.

Generic formula

=TEXTJOIN(separator,1,MID(A1,SEQUENCE(6,1,1,2),2))

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"
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.