Excel – Formatting MAC/WWN with Colons

Often you will receive data about machine addresses or world wide names from one source without any colons and then another source will have these addresses formatted with colons. You can either remove the colons from one but if you have to provide input from your data in a colon delimited format for another application you will have to add the colons after every two characters. The easiest way to do this is with the following formula:

=TEXTJOIN(":",TRUE,MID(A1,SEQUENCE(1,LEN(A1)/2,1,2),2))

This assumes that your data is in cell A1, you will need to adjust accordingly to your worksheets layout. The formula uses the sequence function to create an array of numbers for every other character according to the length of the data in the cell with a step of two. This will form the starting number of every two characters used by the mid function in array mode. From that number it will then extract the value at that point for the length specified.