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.

Alternatively you can create a Lambda named function. Under the Formula menu item in Excel you can select a range of cells and choose define name so that you can refer to it else where. But what you can also do is create a name that behaves like a function. Open Name Manager and add a name AddrDlim and paste into the reference field the formula below. Then you can reuse this formula against any reference just by using =MacDelim(“:”, Ref).

=LAMBDA(vDlim, vAddr, TEXTJOIN(vDlim,TRUE,MID(vAddr,SEQUENCE(1,LEN(vAddr)/2,1,2),2)))