Inserting Colons into a list of Mac Addresses

Question

I have over 500 MAC addresses and I'm trying to find a simple way to insert colons between every 2 characters.

Solution 1

You could get notepad++ and do a search and replace with regex like search for (..) and replace with \1:

Solution 2

It maybe overkill, but I would use Excel. Paste your MAC addresses into column A and this formula into column B:

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)&":"&MID(A1,9,2)&":"&RIGHT(A1,2)

Then, you can copy column B, and either paste special...values into column C or just paste into Notepad.

Solution 3

You could use a sed command such as this:

sed 's/\(\w\w\)\(\w\w\)\(\w\w\)\(\w\w\)\(\w\w\)\(\w\w\)/\1:\2:\3:\4:\5:\6/g' filename

This will just pull out 12 characters in groups of two, and spit them back out with colons in the middle. You could also try a simpler pattern like s/(\w\w)/\1:/g, though this will leave you with an extra colon at the end of every address.

Solution 4

=CONCATENATE(MID(A2;1;2);":";MID(A2;3;2);":";MID(A2;5;2);":";MID(A2;7;2);":";MID(A2;9;2))

This should help in OpenOffice

=CONCATENATE(MID(A7,1,2),":",MID(A7,3,2),":",MID(A7,5,2),":",MID(A7,7,2),":",MID(A7,9,2),":",MID(A7,11,2))

Here it is in excel, i used your answer above and built off it, Thanks

Solution 5

Matt's solution using Notepad++, but fine-tuned to only change lines that (only) contain a mac address:

FIND: ^(..)(..)(..)(..)(..)(..)$

REPLACE: \1:\2:\3:\4:\5:\6

View more solutions

Comments

[SingleNegationElimination]

Beat me to it! In re to your last sentance, you could then use another sed command to strip the trailing colon, and it might still be shorter.

[Jonathan Leffler]

Why would '\1-' insert a colon?

[Jonathan Leffler]

Not all versions of 'sed' support '\w' to mean a word-character. Greater precision in the match ('[0-9a-fA-F]') would make the iterative solution preferable.