Forum Discussion

ronlwb's avatar
ronlwb
Copper Contributor
Nov 03, 2019

Adding a colon to every 2 characters within a cell

Hi all,

 

I have a column of data containing mac addresses eg aabbccddeeff which i extracted from my wireless LAN controller.

I would like to know how to add a colon to every 2x characters, eg aa:bb:cc:dd:ee:ff so that i can import them to my network access control. Please advise. TIA!

excel

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 03, 2019

    ronlwb 

    Or

    =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A1,3,0,":"),6,0,":"),9,0,":"),12,0,":"),15,0,":")
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 03, 2019

    ronlwb 

     

    Try this one, assuming aabbccddeeff is in A1

     

    =MID(A1,1,2)&":"&MID(A1,3,2)&MID(A1,5,2)&":"&MID(A1,7,2)&MID(A1,9,2)&":"&MID(A1,11,2)

     

    A quick and dirty solution. But it works 🙂

Resources