Converting MAC adress Colon notation to Dot Notation

I have have a long list of IP addresses with the following notation:
XX:XX:XX:XX:XX:XX

I want to have that converted to:
XXXX.XXXX.XXXX

I have a lot of MAC addresses I need to trace to find out which port they are behind. But to do this I first want to reformat the whole batch. I have found the following website:
https://www.vultr.com/tools/mac-converter/?mac_address=08%3A00%3A0F%3A89%3A91%3AAF

But I can do only 1 at the time. I have a few hundred i need to reformat. The website only let's me allow to do one by one.

How can I do this the fastest way? Excel? And please I'm not good with formulas ( in Excel), so a little help is appreciated. Also my skill with scripting are lacking.

Find more posts tagged with

Comments

networker050184

Are you trying to convert a MAC to a different format or find the IP for a MAC? A MAC can not just be translated into the corresponding IP.

cyberguypr

If I understand correctly you want to convert a list of AA:BB:CC: DD:EE:FF to AA.BB.CC.DD.EE.FF. Right? I am unsure if you are trying to automate something or if we are talking about a one-time thing. If the list is static you can do this in Excel and just do a FIND for the colon character and REPLACE with the dot character. If automating I am sure there's some very basic script out there if you look for it.

pinkiaiii

you need someone who can do simple php script just to emit every 1,3,5 : symbol ,if you have some time id look at this excel examples,since seems not so hard if you can crack it,and make your output as you desire: https://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/

networker050184

If that's all you want to do you can just use a text editor with column edit and be done with it in two seconds.

FrankGuthrie

I have notepad ++, but note sure how to actually do it, maybe you have some more clues?

I found this website:
http://www.winko-erades.nl/index.php?option=com_content&view=article&id=22:convert-mac-address-in-ms-excel-or-oo-spreadsheet&catid=10&Itemid=5

But editing the formula (using Excel) I get an error message. They give the following example:
=SUBSTITUTE(A2;"-";":")
[FONT=Tahoma, Verdana, Arial, sans-serif]=SUBSTITUTE(A2;":";"-")[/FONT]

[FONT=Tahoma, Verdana, Arial, sans-serif]The fist one substitutes - for :[/FONT]
[FONT=Tahoma, Verdana, Arial, sans-serif]The second one [/FONT]substitutes : for :

So I tried editing the formula:
[FONT=Tahoma, Verdana, Arial, sans-serif]=SUBSTITUTE(A2;":";".") => Colon to Dot[/FONT]

[FONT=Tahoma, Verdana, Arial, sans-serif]This however does not work.[/FONT]

wrwarwick

In Notepad++ if you hold down ALT you can click and make the cursor active for the entire column. You can then just delete what you don't need and edit accordingly.

This might help a bit more - http://docs.notepad-plus-plus.org/index.php/Column_Editing

FrankGuthrie

Yes just wanted to post it that i found out, thx for the help

NetworkNewb

To do what you want in excel you would need

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,":","",5),":",".",4),":","",3),":",".",2),":","",1)

edit: guess I was too late

FrankGuthrie

Thanks everybody Notepad++ saved the day.

NetworkNewb

At least you (tried to) help (ed)