Removing trailing zeros

[punter]

I have about 3000 lines of data in format below:

1000
990000
66000
300

I want to remove the trailing zeros from the number and have the dat
look like this:

1
99
66
3


Does anyone have a formula that will take out the zeros and just leav
with the first numbers?

Thanks a bunch

 

[Norman Harker]

Hi Punter!

Insert a helper column and use the formula:

=--SUBSTITUTE(TEXT(A1,"0000000000"),"0","")
[The number of zeros should be not less than the number of digits in
your largest number]

Copy down
[fast way is to double click the + drag handle]


You can now use:
Select the new column
Copy
Edit > Paste Special > Values > OK

You can now delete the original data or keep it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Cesar Zapata]

Hi,

Try this formula. Lets say that your list starts in A1 then in B1 inser
the formula below then fill down. copy and past values.



=LEFT(A1,FIND(0,A1)-1)


Bye,

Cesar Zapata

 

[Norman Harker]

Hi Cesar!

=--LEFT(A1,FIND(0,A1)-1)

You need the -- if you want numbers.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
this won't create numbers
Try
=--LEFT(A1,FIND(0,A1)-1)

 

[Ron Rosenfeld]

Hi,

Try this formula. Lets say that your list starts in A1 then in B1 inser
the formula below then fill down. copy and past values.



=LEFT(A1,FIND(0,A1)-1)


Bye,

Cesar Zapata

Won't work if a number is of the type: 47010000


--ron

 

[Ron Rosenfeld]

Hi Punter!

Insert a helper column and use the formula:

=--SUBSTITUTE(TEXT(A1,"0000000000"),"0","")
[The number of zeros should be not less than the number of digits in
your largest number]

Copy down
[fast way is to double click the + drag handle]


You can now use:
Select the new column
Copy
Edit > Paste Special > Values > OK

You can now delete the original data or keep it.

If there is an included 0, the formula fails. You remove the included 0 as
well as the trailing 0's.


--ron

 

[Dave R.]

How then to find the rightmost non 0 digit, then cut it off there?

 

[Jason Morin]

=LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
*1<>0,ROW(INDIRECT("1:"&LEN(A1))))))

Array-entered, meaning hold down the SHIFT and CTRL keys,
and then press ENTER.

HTH
Jason
Atlanta, GA

 

[Norman Harker]

Hi Ron!

Nice catch!

If that's possible then an inelegant approach is to use several helper
columns (say 7)
B1:
=IF(INT(A1/10)=A1/10,--LEFT(A1,LEN(A1)-1),A1)
Copy across to G1
H1:
=MIN(A1:G1)
Copy B1:H1 down

Then use Copy > Paste Special > Values > OK on column H and delete
columns A-G

But I'm sure that there's a more elegant formula approach.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Punter!

Insert a helper column and use the formula:

=--SUBSTITUTE(TEXT(A1,"0000000000"),"0","")
[The number of zeros should be not less than the number of digits in
your largest number]

Copy down
[fast way is to double click the + drag handle]


You can now use:
Select the new column
Copy
Edit > Paste Special > Values > OK

You can now delete the original data or keep it.

If there is an included 0, the formula fails. You remove the
included 0 as
well as the trailing 0's.


--ron

 

[Norman Harker]

Hi Jason!

Much more elegant than my brute force approach!

But you need the -- if numbers are wanted.

=--LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1<>0,ROW(INDIRECT("1:"&LEN(A1))))))
Array entered

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

I have about 3000 lines of data in format below:

1000
990000
66000
300

I want to remove the trailing zeros from the number and have the data
look like this:

1
99
66
3


Does anyone have a formula that will take out the zeros and just leave
with the first numbers?

Thanks a bunch.

Norman's solution will work if your numbers have no "included zeros". But if
you have numbers of the form 9904000 that you want to show as 9904, then this
UDF might work:

===============================
Option Explicit

Function DropTrailingZeros(num As Double) As Long
Dim i As Integer

num = Int(num)
For i = 1 To Int(Log(num) / Log(10))
If Int(num / 10 ^ i) <> num / 10 ^ i Then
Exit For
End If
Next i

DropTrailingZeros = num / 10 ^ (i - 1)

If DropTrailingZeros = 10 Then DropTrailingZeros = 1

End Function
==============================

<alt><F11> opens the VB Editor.
Ensure your current project is selected in the Project Explorer window. Then
Insert/Module and paste in the above code.

You can use this as a worksheet function: =DropTrailingZeros(A1)


--ron

 

[punter]

Thanks guys. Once again you came through for me. I hope you and you
families have a happy Easter

 

[Soo Cheon Jheong]

Punter,

If your list starts in A1 then insert a helper column and use the formula
below,

=LEFT(A1,LEN(A1)-MAX((VALUE(RIGHT(A1,ROW($1:$16)))=0)*ROW($1:$16)))

Array entered (Ctrl+Shift+Enter)


and fill down. copy and past values.