Forum Discussion
boodahbellie
Oct 13, 2021Copper Contributor
Format Text and Numbers as a MAC address
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
- mtarlerSilver Contributor
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
- JCassel1234Copper ContributorThanks for this! Just saved me a heck of a time having to manually retype a list of numbers as MACs.