Format Text and Numbers as a MAC address

%3CLINGO-SUB%20id%3D%22lingo-sub-2843451%22%20slang%3D%22en-US%22%3EFormat%20Text%20and%20Numbers%20as%20a%20MAC%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843451%22%20slang%3D%22en-US%22%3E%3CP%3EI%20often%20have%20to%20input%20values%20of%20MAC%20addresses%20from%20electronic%20devices%20for%20tracking%20purposes.%20I%20would%20love%20to%20have%20a%20way%20to%20just%20type%20the%20values%20and%20have%20the%20cell%20automatically%20format%20it%20to%20MAC%20address%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20typing%20001f55417793%20would%20result%20in%26nbsp%3B00%3A1f%3A55%3A41%3A77%3A93%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20looked%20into%20custom%20number%20formats%2C%20but%20that%20doesn't%20work%20because%20of%20the%20occasional%20letters%20in%20a%20MAC%20address.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20here%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2843451%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843775%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Text%20and%20Numbers%20as%20a%20MAC%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598459%22%20target%3D%22_blank%22%3E%40boodahbellie%3C%2FA%3E%26nbsp%3B%20%26nbsp%3Bhere%20is%20a%20way%20you%20can%20have%20an%20'input'%20column%20and%20then%20the%20next%20column%20can%20use%20that%20input%20to%20create%20a%20'properly'%20formatted%20MacID.%3C%2FP%3E%3CP%3EI%20use%20LET()%20and%20such%20so%20it%20requires%20the%20latest%20version%20of%20Excel%20(i.e.%20Excel%20365)%20but%20could%20be%20converted%20to%20an%20older%20version%20if%20needed.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DLET(in%2CA2%2Cinformatted%2CIF(LEN(in)%26lt%3B12%2CREPT(%220%22%2C12-LEN(in))%26amp%3Bin%2Cin)%2CTEXTJOIN(%22%3A%22%2C1%2CMID(in%2CSEQUENCE(6%2C1%2C1%2C2)%2C2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20%22A2%22%20is%20the%20cell%20reference%20and%20then%20the%201st%20thing%20I%20do%20is%20check%2Faccount%20for%20special%20cases%20like%20it%20is%20actually%20all%20numbers%20and%20the%20first%20few%20are%200's%20(i.e.%20excel%20may%20drop%20those%20in%20the%20input%20column).%5C%3C%2FP%3E%3CP%3EAlternatively%20you%20can%20force%20the%20format%20of%20that%20first%20column%20to%20be%20TEXT%20and%20then%20you%20only%20need%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXTJOIN(%22%3A%22%2C1%2CMID(E2%2CSEQUENCE(6%2C1%2C1%2C2)%2C2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESee%20attached%20for%20both%20examples%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I often have to input values of MAC addresses from electronic devices for tracking purposes. I would love to have a way to just type the values and have the cell automatically format it to MAC address format.

 

i.e. typing 001f55417793 would result in 00:1f:55:41:77:93

 

I have looked into custom number formats, but that doesn't work because of the occasional letters in a MAC address.

 

Any help here would be appreciated.

 

I created a macro to do it, but is there a better way?

Sub ConvertToMAC()
    Dim mac, newMAC As String
    Dim i As Integer
    
    mac = Excel.ActiveCell.Value
    newMAC = Mid(mac, 1, 2) & ":" & Mid(mac, 3, 2) & ":" & Mid(mac, 5, 2) & ":" & Mid(mac, 7, 2) & ":" & Mid(mac, 9, 2) & ":" & Mid(mac, 11, 2)
    
    Excel.ActiveCell.Value = newMAC

End Sub
1 Reply

@boodahbellie   here is a way you can have an 'input' column and then the next column can use that input to create a 'properly' formatted MacID.

I use LET() and such so it requires the latest version of Excel (i.e. Excel 365) but could be converted to an older version if needed.

=LET(in,A2,informatted,IF(LEN(in)<12,REPT("0",12-LEN(in))&in,in),TEXTJOIN(":",1,MID(in,SEQUENCE(6,1,1,2),2)))

The "A2" is the cell reference and then the 1st thing I do is check/account for special cases like it is actually all numbers and the first few are 0's (i.e. excel may drop those in the input column).\

Alternatively you can force the format of that first column to be TEXT and then you only need:

=TEXTJOIN(":",1,MID(E2,SEQUENCE(6,1,1,2),2))

See attached for both examples