Forum Discussion

Copper Contributor

Nov 20, 2023

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...

Formulas and Functions

office 365

training

SergeiBaklan

Diamond Contributor

Nov 20, 2023

Lorenzo

I'm sure lambda equivalent was done by someone, here is quickly done variant

TextTrim =
LAMBDA(txt, [ChrToTrim],
    LET(
        chr, IF(ISOMITTED(ChrToTrim), " ", ChrToTrim),
        IF( LEFT(txt)=chr,
            TextTrim( RIGHT( txt, LEN(txt)-1), chr),
            IF( RIGHT(txt) <> chr,
                IF( txt = "", "", txt),
                TextTrim( LEFT( txt, LEN(txt)-1), chr) )
)) )

SergeiBaklan

Diamond Contributor

Nov 20, 2023

TextTrim =
LAMBDA(txt, [ChrToTrim],
    LET(
        chr, IF(ISOMITTED(ChrToTrim), " ", ChrToTrim),
        IF( LEFT(txt) = chr,
            TextTrim( REPLACE(txt,1,1,""), chr),
            IF( RIGHT(txt) <> chr,
                IF( txt = "", "", txt),
                TextTrim( REPLACE(txt,LEN(txt),1,""), chr) )
)) )