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

Lorenzo

Silver Contributor

Nov 20, 2023

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

PQ_RemoveTrailLeadZero.xlsx17 KB

SergeiBaklan
Diamond Contributor
Nov 20, 2023
Lorenzo

(x,y,z)=> Text.Trim(y, z) shall work, isn't it?
- Lorenzo
  Silver Contributor
  Nov 20, 2023
  Good point SergeiBaklan + Thanks. Previous post revised
  - 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) ) )) )