The formula returns the formatted strings as seen in column D. To format a text string with 12 characters in the same way, you can use a formula like this: 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: These are returned directly to the MID function: With the text “112233445566” in B5, the MID function returns an array of 6 strings: This array is returned to the TEXTJOIN function as the text1 argument, and with the colon (:) as the delimiter from C5, we have: 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: SEQUENCE now generates 3 start numbers incremented by 4 characters: And MID returns 3 strings: The TEXTJOIN function then concatenates these strings separated with a dot (.) character:
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.
