Format cells to remove up to 2 trailing zeros

Hello,

Are you totally sure about all the examples you have provided ?

It would seem that dividing by 100 could do the job ... but there is an apparent exception for 110 = 11 ... unless it is 1100 = 11 ...

Try the attached file. Click the button on Sheet1.

Code assigned to the button

Sub RemoveZeros()
    Dim x, i&, ii&
    
    With ActiveSheet.Cells(1).CurrentRegion
        x = .Value
        For i = 1 To UBound(x)
            For ii = 1 To 2
                If Right(x(i, 1), 1) = 0 Then x(i, 1) = Left(x(i, 1), Len(x(i, 1)) - 1)
            Next
        Next
        .Value = x
    End With
    
End Sub

Assuming Google Translate got it right and your post reads:

"Thank you, please ask for a little more help, case 100(2)=1(2), 1800(3)=18(3)"

Then try the attached with code modified to

Sub RemoveZeros()
    Dim x, i&, ii&, s1$, s2$
    
    With ActiveSheet.Cells(1).CurrentRegion
        x = .Value
        For i = 1 To UBound(x)
            s1 = Split(x(i, 1), "(")(0)
            If x(i, 1) Like "*(*)" Then
                s2 = "(" & Split(x(i, 1), "(")(1)
            Else
                s2 = vbNullString
            End If
            For ii = 1 To 2
                If Right(s1, 1) = 0 Then s1 = Left(s1, Len(s1) - 1)
            Next
            x(i, 1) = s1 & s2
        Next
        .Value = x
    End With
    
End Sub

The code will work whether there is a bracketed number (or even bracketed text) after the main number or not.

What was ... All Chinese to me .... is, in fact, ... Vietnamese

LOL, yes it is Vietnamese

Quote from KjBox

LOL, yes it is Vietnamese

Now that you have fully decode this mystery ...

what about =SUBSTITUTE(A1,"00","") ... or to be more precise Hàm SUBSTITUTE

=SUBSTITUTE(A1,"00","") would fail if the number in the brackets was 100, i.e. A1 was "100(100)"

Also fails if A1 is "12003400(3)" since only the last 2 zeros of the unbracketed number need to be removed.

If there is something in brackets, then this should work:

=SUBSTITUTE(SUBSTITUTE(A1,"0(","("),"0(","(")

Yes, Rory, that will remove up to 2 trailing zeros from the unbracketed number and leave anything within the brackets untouched. But, as I understand it, there may not be a bracketed number, in which case no trailing zeros are removed.

I did say "if there is something in brackets"...

If there might not be, then perhaps:

=IF(COUNT(FIND("(",A1)),SUBSTITUTE(SUBSTITUTE(A1,"0(","("),"0(","("),TRIM(SUBSTITUTE(SUBSTITUTE(A1&" ","0 "," "),"0 "," ")))

That works for all scenarios, very neat to add a trailing space before SUBSITUTE if no bracket, then remove the space again with TRIM! Much neater than what I came up with to do it using a formula which was:

=IF(RIGHT(A1,1)=")",SUBSTITUTE(SUBSTITUTE(A1,"0(","("),"0(","("),IF(AND(RIGHT(A1,1)="0",MID(A1,LEN(A1)-1,1)="0"),LEFT(A1,LEN(A1)-2),IF(RIGHT(A1,1)="0",LEFT(A1,LEN(A1)-1),A1)))

However, HGVIET did specify a VBA solution, maybe his actual workbook would not work if an extra column with a formula solution was added, rather than updating the values in the required column in situ.