Help me with vba code to format numbers in the style of 100=1,110=11,1000=10,1800=18...thanks
Format cells to remove up to 2 trailing zeros

HGVIET 
January 4, 2023 at 12:33 PM 
Thread is marked as Resolved.



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

Carim
January 4, 2023 at 3:11 PM Changed the title of the thread from “Please help VBA format cells to remove 1 last zero” to “Format cells to remove 1 last zero”. 
Try the attached file. Click the button on Sheet1.
Code assigned to the button


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
Code
Display MoreSub 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.


KjBox
January 5, 2023 at 7:15 AM Changed the title of the thread from “Format cells to remove 1 last zero” to “Format cells to remove up to 2 trailing zeros”. 
What was ... All Chinese to me .... is, in fact, ... Vietnamese

LOL, yes it is Vietnamese

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.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!