Excel VBA code for MID/Splitting text in cell based on fixed width

It sounds like what you're after could be achieved by the Text to Columns tool. I'm not sure whether you're trying to include this as a step in an existing macro, or if this is all you want the macro to do, so I'll give you both answers.

If you're just looking to split the text at a specified point, you can use the Text to Columns tool. Highlight the cells you want to modify, then go to the Data tab and select "Text to Columns" from the "Data Tools" group.

In the Text to Columns wizard, select the "Fixed Width" radio button and click Next. On step 2, click in the data preview to add breaks where you want the data to be split - so, in the example you gave above, click between "760" and "-". Click Next again.

On step 3, you can choose the format of each column that will result from the operation. This is useful with the leading zeroes you mentioned - you can set each column to "Text". When you're ready, click Finish, and the data will be split.

You can do the same thing with VBA using a fairly simple bit of code, which can be standalone or integrated into a larger macro.

Sub RunTextToColumns()
    Dim rngAll As Range

    Set rngAll = Range("A1", "A970000")
    rngAll.TextToColumns _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(3, 2))

    With Sheets("Sheet4").Range("A1", "A970000")
        .Value = Range("A1", "A970000").Value
        .Offset(0, 1).Value = Range("B1", "B970000").Value
    End With
End Sub

This takes around a second to run, including the split and copying the data. Of course, the hard-coded references to ranges and worksheets are bad practice, and should be replaced with either variables or constants, but I left it this way for the sake of clarity.

I think there's an array formula thing that would do this, but I prefer the brute force approach. There are two ways to fill in the fields, with a procedure or with a function. I've done both, to illustrate them for you. As well, I've purposely used a number of ways of referencing the cells and of separating the text, to illustrate the various ways of achieving your goal.

Sub SetFields()
Dim rowcounter As Long, lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'get the last row in column "C"

For rowcounter = 1 To lastrow 'for each row in the range of values
    'put the left part in column "D"
    ActiveSheet.Range("D" & rowcounter) = FieldSplitter(ActiveSheet.Cells(rowcounter, 3).Text, True)
    'and the right part in the column two over from colum "C"
    ActiveSheet.Cells(rowcounter, 3).Offset(0, 2) = FieldSplitter(ActiveSheet.Cells(rowcounter, 3).Text, False)
Next rowcounter
End Sub
Function FieldSplitter(FieldText As String, boolLeft As Boolean) As String
If boolLeft Then
    FieldSplitter = Mid(FieldText, 2, 3) 'one way of getting text from a string
Else
    FieldSplitter = Left(Right(FieldText, 16), 5) ' another way
End If
  'Another useful function is Split, as in myString = Split (fieldtext, "-")(0) This would return "4760"

End Function