Converting MAC adress Colon notation to Dot Notation

FrankGuthrieFrankGuthrie Posts: 245Member
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.

Comments

  • networker050184networker050184 Posts: 11,962Mod Mod
    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.
    An expert is a man who has made all the mistakes which can be made.
  • cyberguyprcyberguypr Senior Member Posts: 6,665Mod Mod
    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.
  • pinkiaiiipinkiaiii Posts: 216Member
    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/
  • networker050184networker050184 Posts: 11,962Mod Mod
    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.
    An expert is a man who has made all the mistakes which can be made.
  • FrankGuthrieFrankGuthrie Posts: 245Member
    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]
  • wrwarwickwrwarwick Posts: 104Member
    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
  • FrankGuthrieFrankGuthrie Posts: 245Member
    Yes just wanted to post it that i found out, thx for the help
  • NetworkNewbNetworkNewb They are watching you Posts: 3,137Member ■■■■■■■■■□
    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 icon_sad.gif
  • FrankGuthrieFrankGuthrie Posts: 245Member
    Thanks everybody Notepad++ saved the day.

    NetworkNewb

    At least you (tried to) help (ed) :)
Sign In or Register to comment.