Edward Pamias
asked on
Add : ever two numbers in a cell
All,
I get a spreadsheet every day with over 100 mac address. Unfortunately the mac address don't have the colon separating the numbers. How do I go about formatting a cell to add the colons?
I get a spreadsheet every day with over 100 mac address. Unfortunately the mac address don't have the colon separating the numbers. How do I go about formatting a cell to add the colons?
I assume there may be letters in which case you can't use number formatting, if you have an entry like this in A2
0123456789ab
and you want it to look like this
01:23:45:67:89:ab
You could use this formula in B2
=REPLACE(REPLACE(REPLACE(R EPLACE(REP LACE(A2,3, 0,":"),6,0 ,":"),9,0, ":"),12,0, ":"),15,0, ":")
regards, barry
0123456789ab
and you want it to look like this
01:23:45:67:89:ab
You could use this formula in B2
=REPLACE(REPLACE(REPLACE(R
regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@MartinLiss
I am using office 2010 if that makes a difference. I am getting a compile error. See below
Sub MacAddress()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim V As String
conts COL = 1 ' = column A
lngLastRow = Range("A65536").End(xlUp). Row
For lngIndex = 1 To lngLastRow
V = Cells(lngIndex, COL).Value
Cells(lngIndex, COL).Value = Mid(V, 1, 2) & "-" & Mid(V, 3, 2) & "-" & Mid(V, 5, 2) & Mid(V, 7, 2) & "-" & Mid(V, 9, 2) & "-" & Mid(V, 11, 2)
Next
End Sub
I am using office 2010 if that makes a difference. I am getting a compile error. See below
Sub MacAddress()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim V As String
conts COL = 1 ' = column A
lngLastRow = Range("A65536").End(xlUp).
For lngIndex = 1 To lngLastRow
V = Cells(lngIndex, COL).Value
Cells(lngIndex, COL).Value = Mid(V, 1, 2) & "-" & Mid(V, 3, 2) & "-" & Mid(V, 5, 2) & Mid(V, 7, 2) & "-" & Mid(V, 9, 2) & "-" & Mid(V, 11, 2)
Next
End Sub
I updated the code. Change conts to Const
ASKER
@Martinlis
We are close I am missing a colon between BB and CC
00:aa:bbcc:dd:ee
We are close I am missing a colon between BB and CC
00:aa:bbcc:dd:ee
Sub MacAddress()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim V As String
Const COL = 1 ' = column A
lngLastRow = Range("A65536").End(xlUp).Row
For lngIndex = 1 To lngLastRow
V = Cells(lngIndex, COL).Value
Cells(lngIndex, COL).Value = Mid(V, 1, 2) & ":" & Mid(V, 3, 2) & ":" & Mid(V, 5, 2) & ":" & Mid(V, 7, 2) & ":" & Mid(V, 9, 2) & ":" & Mid(V, 11, 2)
Next
End Sub
ASKER
@ Martinls
I got it.. I put my glasses on this time and saw you were missing a ":" so I added it and it works great! Thanks!
Sub MacAddress()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim V As String
Const COL = 1 ' = column A
lngLastRow = Range("A65536").End(xlUp). Row
For lngIndex = 1 To lngLastRow
V = Cells(lngIndex, COL).Value
Cells(lngIndex, COL).Value = Mid(V, 1, 2) & ":" & Mid(V, 3, 2) & ":" & Mid(V, 5, 2) & ":" & Mid(V, 7, 2) & ":" & Mid(V, 9, 2) & ":" & Mid(V, 11, 2)
Next
End Sub
I got it.. I put my glasses on this time and saw you were missing a ":" so I added it and it works great! Thanks!
Sub MacAddress()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim V As String
Const COL = 1 ' = column A
lngLastRow = Range("A65536").End(xlUp).
For lngIndex = 1 To lngLastRow
V = Cells(lngIndex, COL).Value
Cells(lngIndex, COL).Value = Mid(V, 1, 2) & ":" & Mid(V, 3, 2) & ":" & Mid(V, 5, 2) & ":" & Mid(V, 7, 2) & ":" & Mid(V, 9, 2) & ":" & Mid(V, 11, 2)
Next
End Sub
ASKER
Thank you very much! This worked great!
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2012
Marty - MVP 2009 to 2012
The Format function is another option:
Cells(lngIndex, COL).Value = Format(V, "&&:&&:&&:&&:&&:&&")
Cells(lngIndex, COL).Value = Format(V, "&&:&&:&&:&&:&&:&&")
Open in new window