I need to be able to paste a MAC address into a column/cell and have it automatically insert the colon, then I need to be able to copy the MAC address and not the Formula.
Example
A0B1C2D3E4F5 needs to be A0:B1:C2:D3:E4:F5
FYI MAC addresses are always 12 characters long.
Thank You
The attached has event code that will change any 12 character entry in column A into a MAC address.
Bernie Deitrick
Excel MVP 2000-2010
Thank You. it works great if I just paste 1 at a time. Is there any way to have it if I copy a 2-2000 it will switch them all?
Of course. Change the event code to this:
Please Login or Register to view this content.
Great job!! Almost Perfect.
If the MAC begins with 00 (002313215614) it shows up as 2313215614. Not 00:23:13:21:56:14
The code works fine for me in those cases. Try formatting your base column as "General" or as "Text" rather than as number.
Your right. I have the column set to text but some times it switches the cell to General when I post 00 in it. But I have it working.
Cannot Thank You enough!!!!
Please Login or Register to view this content.
Last edited by BMV; 01-05-2022 at 01:36 AM.
If you want it to work with any formatting, you could modify the code to this - works with any number of leading 0s
Please Login or Register to view this content.
Last edited by Bernie Deitrick; 01-05-2022 at 09:49 AM.
@Bernie Deitrick Target.Value = Left( ?????? there must be c.Value i mean.
strNew is created using c.Value, then padded to the left with any needed leading 0s and colons.
@Bernie Deitrick i speak about
that must bePlease Login or Register to view this content.
how ewer my #8 easyPlease Login or Register to view this content.
FormulaFormula:
Please Login or Register to view this content.
Doh! Thanks - I fixed my clearly brain-addled post
Sorry - clearly I need more coffee!
for old excel
Formula:Please Login or Register to view this content.
or
Formula:Please Login or Register to view this content.
but to be sure then 12 characters in place
=REPLACE ( REPLACE ( REPLACE ( REPLACE (REPLACE ( RIGHT(REPT(0;12)&A2;12);11; ;":");9; ;":");7; ;":");5; ;":");3; ;":")
Last edited by BMV; 01-05-2022 at 10:31 AM.
Power Query
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks