cell formatting

**xtrmhyper** · 01-18-2007, 04:46 AM

how can i format the cell if type 00146f7ebg96
out put is 00:14:6f:7e:bg:96 automatic

tnx in advance

godspeed

**xtrmhyper** · 01-18-2007, 05:10 AM

up

any idea?

**arthurbr** · 01-18-2007, 05:31 AM

You will need VBA or a formula in another cell

**xtrmhyper** · 01-18-2007, 05:37 AM

what is the command?

tnx

**Bryan Hessey** · 01-18-2007, 06:11 AM

Originally Posted by xtrmhyper

what is the command?

tnx

Hi

in the worksheet tab, rightmouse the tab and select View Code and paste this into there

Private Sub worksheet_change(ByVal Target As Range)
Dim myV As String, myValue As String, mc As String
If Target.Cells.count = 1 And Target.Column = 1 And Not IsEmpty(Target) Then
    mc = ":"
    myV = Target.Value
    If Len(myV) = 12 Then
        myValue = Mid(myV, 1, 2) & mc & Mid(myV, 3, 2) & mc & Mid(myV, 5, 2) & mc & Mid(myV, 7, 2) & mc & Mid(myV, 9, 2) & mc & Mid(myV, 11, 2)
        Target.Value = myValue
        End If
    End If
End Sub

any single cell 12 figure entry will be converted, but you will not be able to use the field as = 00146f7ebg96 for comparison or lookup etc.

hth
---