Entering a mac address in a cell

  • Thread starter Thread starter richdsn2
  • Start date Start date
R

richdsn2

I want to enter a series of 50 mac addresses into an Excel spreadsheet. How
do I format the cell so that all I have to do is enter the hexidecimal
numbers, and Excel will automatically enter a colon between each set of two
numbers?
 
Use a different column (A:D) for each octet.

Then use a formula in E to combine the string.
=a1&":"&b1&":"&c1&":"&d1
or
=hex2dec(a1)&":"&hex2dec(b1)&":"&hex2dec(c1)&":"&hex2dec(d1)
or
=text(hex2dec(a1),"000")&":"
&text(hex2dec(b1),"000")&":"
&text(hex2dec(c1),"000")&":"
&text(hex2dec(d1),"000")

If you use the formulas that have =hex2dec() in them, remember that you have to
have the analysis toolpak installed (xl2003 and below).
 
I understand I could type each octet into a different cell, and then use a
formula to combine them into one cell. But that would defeat the object of
what I want to do.
I want to type a string of numbers, i.e. 001AC32BBA22 by using number pad
and keyboard. Then have Excel post the number in one cell as
00:1A:C3:2B:BA:22. Converting the numbers to decimal is not an option, I
don't want decimal, I want the mac in hex actual.

Thank you for your help and assistance.

Maybe my question was not phrased correctly. I want to enter a string of
alpha/numeric text characters, and have Excel automatically separate the
string into groups of two text characters, separated by a colon (:).
 
If you have at least one alpha character in your MAC address (I gave you
formulas for IP addresses--sorry), you could use another formula to split them.

(If you have a MAC address that is all numeric, make sure you enter it as text
by preceding the entry with an apostrophe or preformatting the cell as Text.)

Then in an adjacent cell:
=left(a1,2)&":"&mid(a1,3,2)&":"&mid(a1,5,2)&":"&mid(a1,7,2)&":"&mid(a1,9,2)

You could actually have an event macro that does this parsing, but I'd use the
formula.
 
I'm not sure you can format a cell to do this, but you can do it with a
Worksheet change event. Right click on the sheet tab and view code. Paste
this in the sheet module. You may need to modify so that it doesn't parse
every cell with 12 characters.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Len(Target.Text) = 12 Then
For i = 1 To 11 Step 2
If mystring = "" Then
mystring = Mid(Target, i, 2)
Else
mystring = mystring & ":" & Mid(Target, i, 2)
End If
Next i
Target = mystring
End If
Application.EnableEvents = True

End Sub
 
Back
Top