Reformatting MAC addresses in Excel

Today, a customer asked me to add his 150 phone’s MAC address to their MAB Bypass database they use for 802.1x. The customer game me the output from an Excel spreadsheet that contained the MAC addresses but without the “-“ in it. I needed to get it in the 01-11-11-11-10-00 format.

Here is the Excel formula:

=MID(A2,1,2)&”-“&MID(A2,3,2)&”-“&MID(A2,5,2)&”-“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2)

Here is how it works. Take a look at the example MAC addres (Picture below). You can see that the MAC address is a 12 character line.

 

Explanation of Formula:
=MID (text, start_num, num_chars)&”-“

Excerpt from my formula:
=MID(A2,1,2)&”-“
What this says is: start at the 1st character ‘0’ and move ‘2’ characters over ‘&’ add a ‘-‘

If you were to leave this formula the way it is, you would have the following output:

However, we added another ‘&’ and another sub formula ‘MID(A2,3,2)&”-“‘

The above section will start at position ‘3’ and move 2 characters over. Then it will add a ‘-‘ after the 5th character (3+2).

We then repeat the same operation with 5,7,9,and 11, adding a ‘-‘ between each except for the last one (11).

 

The screenshot above shows the final output.