Leading and Trailing Zero in Excel

DrExcel_Excel_MVP 

A VBA solution:

Function RemoveLeadingAndTrailingZeros(s As String) As String
    Dim i As Long
    Dim r As String
    r = s
    Do While Left(r, 1) = "0"
        r = Mid(r, 2)
    Loop
    Do While Right(r, 1) = "0"
        r = Left(r, Len(r) - 1)
    Loop
    RemoveLeadingAndTrailingZeros = r
End Function

Use like this:

=RemoveLeadingAndTrailingZeros(B6)