Options

Converting MAC adress Colon notation to Dot Notation

FrankGuthrieFrankGuthrie Member Posts: 245
in CCNA & CCENT
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.
· Share on FacebookShare on Twitter

Comments

  • Options
    networker050184networker050184 Mod Posts: 11,962 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.
    · Share on FacebookShare on Twitter
  • Options
    cyberguyprcyberguypr Mod Posts: 6,928 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.
    · Share on FacebookShare on Twitter
  • Options
    pinkiaiiipinkiaiii Member Posts: 216
    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/
    · Share on FacebookShare on Twitter
  • Options
    networker050184networker050184 Mod Posts: 11,962 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.
    · Share on FacebookShare on Twitter
  • Options
    FrankGuthrieFrankGuthrie Member Posts: 245
    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]
    · Share on FacebookShare on Twitter
  • Options
    wrwarwickwrwarwick Member Posts: 104
    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
    · Share on FacebookShare on Twitter
  • Options
    FrankGuthrieFrankGuthrie Member Posts: 245
    Yes just wanted to post it that i found out, thx for the help
    · Share on FacebookShare on Twitter
  • Options
    NetworkNewbNetworkNewb Member Posts: 3,298 ■■■■■■■■■□
    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
    · Share on FacebookShare on Twitter
  • Options
    FrankGuthrieFrankGuthrie Member Posts: 245
    Thanks everybody Notepad++ saved the day.

    NetworkNewb

    At least you (tried to) help (ed) :)
    · Share on FacebookShare on Twitter
Sign In or Register to comment.