Forum Discussion
Format Text and Numbers as a MAC address
I often have to input values of MAC addresses from electronic devices for tracking purposes. I would love to have a way to just type the values and have the cell automatically format it to MAC addres...
boodahbellie here is a way you can have an 'input' column and then the next column can use that input to create a 'properly' formatted MacID.
I use LET() and such so it requires the latest version of Excel (i.e. Excel 365) but could be converted to an older version if needed.
=LET(in,A2,informatted,IF(LEN(in)<12,REPT("0",12-LEN(in))&in,in),TEXTJOIN(":",1,MID(in,SEQUENCE(6,1,1,2),2)))The "A2" is the cell reference and then the 1st thing I do is check/account for special cases like it is actually all numbers and the first few are 0's (i.e. excel may drop those in the input column).\
Alternatively you can force the format of that first column to be TEXT and then you only need:
=TEXTJOIN(":",1,MID(E2,SEQUENCE(6,1,1,2),2))See attached for both examples
Thanks for this! Just saved me a heck of a time having to manually retype a list of numbers as MACs.