Forum Discussion
Leading and Trailing Zero in Excel
How to Remove Leading and Trailing Zeros in Excel We have a set of product codes like this : 000P2I290002M900 we want to make it like this P2I290002M9 which means removing all leading and trai...
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".