Leading and Trailing Zero in Excel

Copper Contributor

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 trailing zeros from the code 

 

2023-11-20_082808.png

All my attempts to solve this 

 

 

=MAP(

    B6:B14,

    LAMBDA(x,

        LET(

            a, SUBSTITUTE(x, 0, ""),

            b, MID(x, SEQUENCE(LEN(x)), 1),

            MID(

                LEFT(x, XMATCH(RIGHT(a), b, , -1)),

                MATCH(LEFT(a), b, 0),99))))

 

 

 

=MAP(

    B6:B14,

    LAMBDA(x,

        LET(

            a, TEXTSPLIT(x, , 0),

            TEXTJOIN(0, 0,DROP(

              DROP(a, MATCH(FALSE, a = "", 0) - 1),

              XMATCH(FALSE, a = "", , -1) - COUNTA(a))))))

 

               

 

=MAP(

    B6:B14,

    LAMBDA(x,

        LET(

            a, SUBSTITUTE(x, 0, ""),

            b, SEQUENCE(LEN(x)),

            s, HSTACK(LEFT(a), RIGHT(a)),

            t, MID(x, b, 1),

            f, TOCOL(FIND(s, t) + b - 1, 3),

            MID(x, MIN(f), MAX(f) - MIN(f) + 1))))

 

 

 

 =MAP(

    B6:B14,

    LAMBDA(x,

        LET(

            a, SUBSTITUTE(x, 0, ""),

            b, FIND(LEFT(a), x),

            MID(x,b, MAX(TOCOL(

           FIND(RIGHT(a), MID(x, b, LEN(x)),

           SEQUENCE(LEN(x))),3))))))

          

                       

                   

 

 

=MAP(

    B6:B14,

    LAMBDA(x,

        LET(

            a, TEXTSPLIT(x, , 0),

            b, XMATCH(FALSE, a = "", 0, 1),

            TEXTJOIN(0,0,CHOOSEROWS(a,

             SEQUENCE(XMATCH(FALSE, a = "",

             0, -1) - b + 1, , b))))))

 

My LinkedIn :

https://www.linkedin.com/in/hazemhassandrexcel

My YouTube Channel :
https://www.youtube.com/DroosHROnline

 

#Hazem_Hassan #Dr_Excel #excel #excelskills #exceltricks #exceltips

 

               

 

21 Replies

@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)

@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 

Wouldn't this one work? Similar to @Peter Bartholomew '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".

Riny_van_Eekelen_0-1700480073775.png

 

@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 

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

 

 

@Lorenzo 

(x,y,z)=> Text.Trim(y, z) shall work, isn't it?

Good point @SergeiBaklan + Thanks. Previous post revised

@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) )
)) )

Or

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) )
)) )
=WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~^0+|0+$~~" & A2)

@SergeiBaklan and @DrExcel_Excel_MVP here's another recursive lambda method that uses fixed-point combinator logic to define and use the custom function directly in a cell:

 

=LET(
F, LAMBDA(X,s,c, LET(
   a, IF(LEFT(s)=c, RIGHT(s, LEN(s)-1), s),
   b, IF(RIGHT(a)=c, LEFT(a, LEN(a)-1), a),
   IF(OR(LEFT(b)=c, RIGHT(b)=c), X(X, b, c), b))),
F(F, B6:B14, "0"))

 

...where s is the string, cell or range of values to be cleaned, and c is the character to be removed (leading/trailing).

 

Example: In-Cell Recursive Lambda FunctionExample: In-Cell Recursive Lambda Function

@djclements 

The only it returns zero for the blank cell, better empty string

@SergeiBaklan It will only return a zero when used to evaluate a single blank cell, which kinda defeats the purpose of the function, so it's not really a concern. When using it on a range, however, if any of the cells within the range are blank, it returns an empty string for those cells. It also returns an empty string for any cells that contain all zeros (ie: "0000").

 

The main benefits of this method include the following:

  • it can accept a range of values and spill the results, without using BYROW
  • there is no need to create the custom lambda function in Name Manager, if you only need to use it once within the workbook

@djclements 

Ok, good.

I stopped using in-cell lambdas after AFE appeared due to poor maintainability and scalability, but in some cases why not.

@djclements 

Since you have raised the bar, the following function

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

 returns a null string for blank members of the list or for any that comprises nothing but 0s.  If MAP is a problem it can be hidden within a further LAMBDA function.

 

image.png

 

@SergeiBaklan No worries... whatever works for you. =) I haven't used AFE or Excel Labs yet, because I find it easy enough to build and test custom lambda functions directly in a worksheet, then copy them to Name Manager if they'll be used multiple times in the workbook.

 

@Peter Bartholomew My main objective was to find a way to spill the results for the entire range using standard functions. It was merely a happy accident that it also returned an empty string for blank cells within the range, lol. Nice addition to cover that scenario in yours too.

 

As an aside, the two key elements that I found to be responsible for making the recursive lambda function spill the correct results for the entire range include:

 

  • using the LET function to declare variables for the LEFT / RIGHT calculations first, then passing those variables to the next iteration of the function (as opposed to passing the calculation directly)
  • using the OR function as part of the logical test to exit the recursive function

 

If either of the above methods were not used, it would return the #NUM! error or incorrect results, when attempting to pass it the entire range of values. For example, consider the following versions of a simplified LTrim function and the different results they produce:

 

=LTrim(B6:B14, "0")

 

Version 1: no LET statement and no OR function used; returns #NUM!

 

LTrim =
LAMBDA(str,chr,
   IF(LEFT(str)=chr, LTrim(RIGHT(str, LEN(str)-1), chr), str)
)

 

Version 2: using LET statement, but no OR function; returns #NUM!

 

LTrim =
LAMBDA(str,chr,
   LET(
      a, IF(LEFT(str)=chr, RIGHT(str, LEN(str)-1), str),
      IF(LEFT(a)=chr, LTrim(a, chr), a)
   )
)

 

Version 3: using OR function, but no LET statement; returns INCORRECT results

 

LTrim =
LAMBDA(str,chr,
   IF(OR(LEFT(str)=chr), LTrim(RIGHT(str, LEN(str)-1), chr), str)
)

 

Version 4: using LET statement and OR function; returns CORRECT results

 

LTrim =
LAMBDA(str,chr,
   LET(
      a, IF(LEFT(str)=chr, RIGHT(str, LEN(str)-1), str),
      IF(OR(LEFT(a)=chr), LTrim(a, chr), a)
   )
)

 

All of the above versions work correctly when used with a single cell; however, only Version 4 will spill the correct results when used with a range. Not exactly sure what the reason is, but I thought it was interesting. Cheers!

@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, ""))
    )
)

@Patrick2788  Perhaps we should leave it here, The OP doesn't seem to be the least bit interested in this discussion after all.

@djclements 

OR(LEFT(str)=chr) returns single Boolean value, TRUE if at least one text starts from chr.

LEFT(str)=chr returns an array of boolean values, for each of them IF repeats LTrim or return a. That obviously doesn't work.