Remove trailing zeros

[FryGirl]

How can I remove the trailing zeros from the inputs below? Everything to the right is what I would like to keep

D000000000 -- D
D0SA000000 -- D0SA
D1A0000000 -- D1A

 

[djclements]

A basic recursive lambda option for MS365 (defined in Name Manager):

RTrimChar

=LAMBDA(text,char,IF(RIGHT(text)=char,RTrimChar(LEFT(text,LEN(text)-1),char),text))

Works with one text value at a time. For example:

=RTrimChar(A2,"0")

-OR-

=MAP(A2:A4,LAMBDA(v,RTrimChar(v,"0")))

You could also set "0" as the default char:

RTrimChar

=LAMBDA(text,[char],LET(char,IF(ISOMITTED(char),"0",char),IF(RIGHT(text)=char,RTrimChar(LEFT(text,LEN(text)-1),char),text)))

Which would help to simplify its use with MAP:

=MAP(A2:A4,RTrimChar)

But it's also possible to make the custom function accept an array of text strings (without MAP):

RTrimChar

=LAMBDA(text,[char],LET(char,IF(ISOMITTED(char),"0",char),test,RIGHT(text)=char,IF(OR(test),RTrimChar(IF(test,TEXTBEFORE(text,char,-1),text),char),text)))

Example:

=RTrimChar(A2:A4)

For older versions of Excel, a basic user-defined function in VBA:

Function RTrimChar(text As String, char As String) As String
    Dim str As String: str = text
    Do Until Right(str, 1) <> char
        str = Left(str, Len(str) - 1)
    Loop
    RTrimChar = str
End Function

 

[FryGirl]

Thank you all for the solutions. I ended up using the UDF which worked great.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[fahad_ibnfurjan]

Remove trailing zeros.xlsx
	A	B
1	D000000000	D
2	D0SA000000	D0SA
3	D1A0000000	D1A
Sheet!

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(IF((MID($A1,SEQUENCE(LEN($A1)),1)="0")+(MID($A1&0,SEQUENCE(LEN($A1))+1,1)="0")<>2,MID($A1,SEQUENCE(LEN($A1)),1),""))

 

[jimrward]

With many decades in software development and coding I prefer the KISS principle

 

[Scott Huish]

A 1-line UDF:

Function RTrimChar(text As String, char As String) As String
    RTrimChar = Replace(RTrim(Replace(text, char, " ")), " ", char)
End Function

 

[Peter_SSs]

Another option using standard worksheet functions (avoiding REGEX) and also works if less than 3 zeros at the end (re post 4) or if multiple consecutive internal zeros exist (re post 14).
Column B to copy down or column C for spill results.

FryGirl.xlsm
	A	B	C
1	Data	Result 1	Result 2
2	D000000000	D	D
3	D0SA000000	D0SA	D0SA
4	D1A0000000	D1A	D1A
5
6	XYZ	XYZ	XYZ
7	A0B0	A0B	A0B
8	D00SA000000	D00SA	D00SA
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=MAP(A2:A8,LAMBDA(r,LET(n,LEN(r)+1,s,SEQUENCE(n),LEFT(r,n-AGGREGATE(14,6,IF(-RIGHT(r&0,s)=0,s),1)))))
B2:B8	B2	=LET(n,LEN(A2)+1,s,SEQUENCE(n),LEFT(A2,n-AGGREGATE(14,6,IF(-RIGHT(A2&0,s)=0,s),1)))
Dynamic array formulas.

 

[Peter_SSs]

Just realised that my formulas above would fail for data like row 9 below so revised options in cols D:E

FryGirl.xlsm
	A	B	C	D	E
1	Data	Result 1	Result 2	Result 3	Result 4
2	D000000000	D	D	D	D
3	D0SA000000	D0SA	D0SA	D0SA	D0SA
4	D1A0000000	D1A	D1A	D1A	D1A
5
6	XYZ	XYZ	XYZ	XYZ	XYZ
7	A0B0	A0B	A0B	A0B	A0B
8	D00SA000000	D00SA	D00SA	D00SA	D00SA
9	D0E000000	D	D	D0E	D0E
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=MAP(A2:A9,LAMBDA(r,LET(n,LEN(r)+1,s,SEQUENCE(n),LEFT(r,n-AGGREGATE(14,6,IF(-RIGHT(r&0,s)=0,s),1)))))
E2:E9	E2	=MAP(A2:A9,LAMBDA(r,LET(n,LEN(r)+1,s,SEQUENCE(n),LEFT(r,n-AGGREGATE(14,6,IF(RIGHT(r&0,s)=REPT(0,s),s),1)))))
B2:B9	B2	=LET(n,LEN(A2)+1,s,SEQUENCE(n),LEFT(A2,n-AGGREGATE(14,6,IF(-RIGHT(A2&0,s)=0,s),1)))
D2:D9	D2	=LET(n,LEN(A2)+1,s,SEQUENCE(n),LEFT(A2,n-AGGREGATE(14,6,IF(RIGHT(A2&0,s)=REPT(0,s),s),1)))
Dynamic array formulas.

 

[Scott Huish]

Book2
	A	B
1	Data
2	D000000000	D
3	D0SA000000	D0SA
4	D1A0000000	D1A
5	XYZ	XYZ
6	A0B0	A0B
7	D00SA000000	D00SA
8	D0E100000	D0E1
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IFNA(LEFT(A2,XMATCH(TRUE,--(MID(A2,SEQUENCE(LEN(A2)),LEN(A2)))=0)-1),A2)

 

[Peter_SSs]

Hi Scott
Not sure if it would be possible with the OP's data but that fails with the A9 sample data from my previous post or, say, "D50E120" - same reason that I changed from my post 17 formula to that in post 18.