macro to split data to respective columns not working as expected

I have column A which has a set of data which needs to be split to their respective headers column B /C D.

Row1 represents the headers.

In applying 3 different macros with same output,not giving expected results

1 first giving compile error variable not defined.

Sub split()
Dim lr As Long
Dim myrange As Range, cell As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
lr = Cells(Rows.Count, 1).End(xlUp).Row

Set myrange = Range("A2:A" & lr)
Cells(1, 3) = "Tariff No": Cells(1, 4) = "Description": Cells(1, 5) = "Origin"
For Each cell In myrange
    txt = cell.Text
    cell.Offset(, 2) = Left(txt, 8)
    cell.Offset(, 3) = Mid(txt, 10, Len(txt) - 18)
    cell.Offset(, 4) = Left(Right(txt, 8), 2)
Next
Range("C1:E" & lr).Columns.AutoFit
End Sub

the second macro giving invalid procedure callor argument rin time error 5

Results(R, 2) = Trim(Mid(Data(R, 1), 9, Len(Data(R, 1)) - 16))

Sub SplitTariffData()
  Dim R As Long, Data As Variant, Results As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  ReDim Results(1 To UBound(Data), 1 To 3)
  For R = 1 To UBound(Data)
    Results(R, 1) = Format(Left(Data(R, 1), 8), "00000000")
    Results(R, 2) = Trim(Mid(Data(R, 1), 9, Len(Data(R, 1)) - 16))
    Results(R, 3) = Left(Right(Data(R, 1), 8), 2)
  Next
  Range("C2").Resize(UBound(Results)).NumberFormat = "@"
  Range("C2").Resize(UBound(Results), 3) = Results
End Sub

3 rd macro split data incorrectly

Sub SplitIt()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 2).Value = Evaluate("if(row(),""'"" & left(" & .Address & ",8),"""")")
    .Offset(, 3).Value = Evaluate("if(row(),mid(" & .Address & ",10,len(" & .Address & ")-18),"""")")
    .Offset(, 4).Value = Evaluate("if(row(),left(right(" & .Address & ",8),2),"""")")
  End With
End Sub