Custom Format MAC address

[wpryan]

Hi All, I'm working on a document which requires a user to enter the MAC address of a computer. The current document requires they put these 6 values in separate cells. For formatting purposes, I'd like it to be in one cell only. Further, I'd like them to be able to just enter the characters, not the separator. I was trying to work this out in Custom Formatting, but wasn't able to. The what should appear should be TT-TT-TT-TT-TT-TT, where TT is two text characters. Thanks in advance.

 

[RoryA]

Custom formatting won't work for that (the separator is usually a colon rather than hyphen is it not?) so you would need to use code to add the separator - would that be acceptable?

 

[wpryan]

Thanks for your reply. Actually, you're right, the separator should be a colon. Can you help out? Thanks in advance.

 

[RoryA]

Right-click the worksheet tab, choose View Code and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    Dim n As Long
    Dim strTemp As String
    Const csMONITOR_RANGE As String = "A1:A100"
    
    On Error GoTo clean_up
    
    If Not Intersect(Target, Range(csMONITOR_RANGE)) Is Nothing Then
        Application.EnableEvents = False
        For Each rngCell In Intersect(Target, Range(csMONITOR_RANGE)).Cells
            If Len(rngCell.Value) = 12 Then
                For n = 1 To 12 Step 2
                    strTemp = strTemp & ":" & Mid$(rngCell.Value, n, 2)
                Next n
                rngCell.Value = Mid$(strTemp, 2)
            End If
        Next rngCell
    End If
    
clean_up:
    Application.EnableEvents = True

End Sub

adjust the red part to match whatever range you need to monitor for input.

 

[wpryan]

Thanks for your help. However, I guess I didn't explain what I want precisely enough. I am not looking to transform 6 individual cells into one cell with a colon separating their values, I'm interested in having a person simply enter 12 characters into a single cell and when they exit, it is formatted so that between every two values there appears a colon.

 

[RoryA]

That's exactly what the code does. I assumed you wanted it to work for a 12 digit entry in a number of cells, but if not, just change the red part to one cell address.

 

[wpryan]

Fantastic, it works just fine. Thanks a lot!

 

[RoryA]

Glad to help.