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

 

[ranman256]

replace Range("A1") with your column

Public Sub ClearZeros()
Dim i As Integer
Dim iRows As Integer
On Error Resume Next

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count

While ActiveCell.Row <= iRows
   ActiveCell.Value = Replace(ActiveCell.Value, "0", "")

   ActiveCell.Offset(1, 0).Select     'next row
Wend
End Sub

 

[FryGirl]

I don't have the second column. This is what I would like a formula to do.

 

[jimrward]

=left(A1,find(“000”,A1)-1)
Note the double quotes are what’s on your keyboard I am using an iPad so they are different

 

[jimrward]

replace Range("A1") with your column

Public Sub ClearZeros()
Dim i As Integer
Dim iRows As Integer
On Error Resume Next

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count

While ActiveCell.Row <= iRows
   ActiveCell.Value = Replace(ActiveCell.Value, "0", "")

   ActiveCell.Offset(1, 0).Select     'next row
Wend
End Sub

Will this not reduce DzeroSA to DSA

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	D000000000	D
3	D0SA000000	D0SA
4	D1A0000000	D1A
Sheet4

Cell Formulas
Range	Formula
B2:B4	B2	=REGEXREPLACE(A2,"0*$","")

 

[FryGirl]

The formula works great. The VBA will reduce D0SA000000 to DSA

 

[FryGirl]

Thanks Fluff, but REGEXREPLACE does not work in my 365 version of Excel.

 

[Fluff]

REGEXREPLACE does not work in my 365 version of Excel

You obviously don't have it yet, it varies depending on what update channel your on.

 

[ranman256]

Public Sub ClearZeros()
Dim vWord
Dim iRows As Integer, i As Integer
On Error Resume Next

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count

While ActiveCell.Row <= iRows
   vWord = Trim(ActiveCell.Value)
   For i = Len(vWord) To 1 Step -1
     If Mid(vWord, i, 1) <> "0" Then Exit For
   Next
   
   ActiveCell.Value = Left(vWord, i)
   
   ActiveCell.Offset(1, 0).Select     'next row
Wend
End Sub