Leading and Trailing Zero in Excel

DrExcel_Excel_MVP 

This is my variant:

=MAP(Strings,Strip)


'Strip
=LAMBDA(str,
    IF(
        AND(RIGHT(str) <> "0", LEFT(str) <> "0"),
        str,
        Strip(REPLACE(str, IF(RIGHT(str) = "0", LEN(str), 1), 1, ""))
    )
)

DrExcel_Excel_MVP 

Power Query for the record

// Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    Fixed = Table.ReplaceValue(Source, each [Product code], "0",
        (x,y,z)=> Text.Trim(y,z),
        {"Product code"}
    )
in
    Fixed

DrExcel_Excel_MVP 

OfficeScript for the collection

function main(workbook: ExcelScript.Workbook) {

  const sheet = workbook
    .getActiveWorksheet()

  sheet
    .getRange("C6:C14")
    .setValues(
      sheet
        .getRange("B6:B14")
        .getValues()
        .map(x => [(x + '').replace(/^0+|0+$/g, "")])
    )
}

DrExcel_Excel_MVP 

Wouldn't this one work? Similar to PeterBartholomew1 's solution though using BYROW.

=BYROW(
    myData,
    LAMBDA(a,
        LET(
            keep, MID(a, SEQUENCE(LEN(a)), 1) <>
                "0",
            first, XMATCH(TRUE, keep, 0, 1),
            last, XMATCH(TRUE, keep, 0, -1),
            return, MID(a, first, last - first + 1),
            return
        )
    )
)

....where the product codes are in the named range "myData".

DrExcel_Excel_MVP 

Hi.  There are similarities.

My formula came out as

= MAP(list, TrimZeroλ)

where TrimZeroλ is given by

= LET(
    nonzero, Explodeλ(text) <> "0",
    first,   XMATCH(TRUE, nonzero),
    last,    XMATCH(TRUE, nonzero, , -1),
    MID(text, first, 1 + last - first)
  )

Explodeλ is pretty standard

= LET(
    n, LEN(text), 
    k, SEQUENCE(n), 
    MID(text, k, 1)
  )

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)