Extract value into columns using VBA

[Vincent88]

Hi Guys,
I want to create a code to split the content of a cell into 3 colurmns but it does not work out. Please help to make it through.

What the code should do is to convert the those numbers in the left to MAC address and put it into column B, and those numbers after "X=" and "Y=" to column C and D respectively.

Here is my unfinished code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText As String
Dim macText As String
Dim c As Range
Dim lrow As Long
Dim EntryMac As Long
Dim EntryX As Long
Dim EntryY As Long


lrow = Range("A1").End(xlDown).Row

If Target.CountLarge > 1 Then Exit Sub
On Error GoTo ErrHandler:


    If Len(Target) < 12 Then Exit Sub  'check for correct length
    If Target.Column <> 1 And Target.Row = 1 Then Exit Sub 'only Column A and Row 2 downwards
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("A2:A" & lrow)) Is Nothing Then
    
    For Each c In Range("b2:b" & lrow)
        
         macText = Left(Target.Value, 12)
         myText = Left(macText.Value, 2) _
        & "-" & Mid(macText.Value, 3, 2) _
        & "-" & Mid(macText.Value, 5, 2) _
        & "-" & Mid(macText.Value, 7, 2) _
        & "-" & Mid(macText.Value, 9, 2) _
        & "-" & Right(macText.Value, 2)
      
    EntryMac = Left(Target, 12)
    EntryX = InStr(Target, "X=" - InStr(Target, "Y="))
    EntryY = InStr(Target, "Y=")
          c.Value = UCase(myText)
        
    
    mcell.Value = macText
    
    Next c
  
    
    Application.EnableEvents = True
End Sub

 

[Vincent88]

Hi Alex, Thank you for your valuable time. It works now except that if in the lower case of "x", the x's value will go to the column of Y. I need to adjust the code to
If Left(arr(1), 1) = "x" Or Left(arr(1), 1) = "X" Then
Is there any simplier way to do the change ?

 

[Alex Blakenburg]

If Left(arr(1), 1) = "x" Or Left(arr(1), 1) = "X" Then
Is there any simplier way to do the change ?

You could also use
If UCase(Left(arr(1), 1)) = "X" Then

 

[Vincent88]

You could also use
If UCase(Left(arr(1), 1)) = "X" Then

Bingo !! Thanks a lot.

 

[Alex Blakenburg]

No problem. Glad we could help.