To insert column and add formula

Sub Insrt_Mat()
Dim Found As Range, LR As Long, iCl As Integer

Set Found = Rows(1).Find(what:="Material Code", LookIn:=xlValues, lookat:=xlWhole)

If Not Found Is Nothing Then
    iCl = Found.Column + 1
    LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
    Columns(iCl).Insert
    Columns(iCl).Clear
    Cells(1, iCl).Value = ""
    With Range(Cells(2, iCl), Cells(LR, iCl))
        .Formula = "=Trim(" & Cells(2, iCl - 1).Address(False, False) & ")"
        .Value = .Value
    End With
End If

End Sub

This line will convert the formula cells as values

.Value = .Value