# Leading and Trailing Zero in Excel

Copper Contributor

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

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

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

21 Replies

# Re: Leading and Trailing Zero in Excel

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
End Function``````

Use like this:

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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, "")])
)
}
``````

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

Good point @SergeiBaklan + Thanks. Previous post revised

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

=WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~^0+|0+\$~~" & A2)

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

@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

# Re: Leading and Trailing Zero in Excel

Ok, good.

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

# Re: Leading and Trailing Zero in Excel

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.

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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

# Re: Leading and Trailing Zero in Excel

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.