VBA-instr(),left(),mid(),right()

Sub matclear()

Dim matname(1000) As String
Dim p1 As Integer
Dim p2 As Integer
Dim i As Integer
Dim length As Integer
Dim tempstr As String
i = 1
Do
If i = 1 Then
p1 = InStr(Cells(i, 1), “[”)
p2 = InStr(Cells(i, 1), “]”)
matname(i) = Mid(Cells(i, 1), p1 + 1, p2 - p1 - 1)
'MsgBox matname(i)
End If

If InStr(Cells(i, 1).Value, “[”) <> 0 And i <> 1 Then

Cells(i + 1, 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
length = Len(Cells(i, 1))
p1 = InStr(Cells(i, 1), “[”)
p2 = InStr(Cells(i, 1), “]”)
Cells(i, 2).Value = Left(Cells(i, 1).Value, p1 - 1)
Cells(i + 1, 1).Value = Mid(Cells(i, 1).Value, p1, p2 - p1 + 1)
tempstr = Right(Cells(i, 1).Value, length - p2 - 1)
p1 = InStr(tempstr, “[”)
p2 = InStr(tempstr, “]”)
Cells(i + 3, 1).Value = Mid(tempstr, p1, p2 - p1 + 1)
length = Len(tempstr)
Cells(i + 4, 1).Value = Right(tempstr, length - p2)
Cells(i, 1).Value = Cells(i, 2).Value
i = i + 3
End If

i = i + 1
If i = 20000 Then
MsgBox “complete!”
Exit Do
End If
Loop

End Sub