Formatting a column without Rounding Up

[JWGoldfinch]

I have a worksheet with over 200,000 rows of data and In one column I have a variety of numbers that are mixed decimals (See Example). I need to format the column to Truncate everything right of the decimal. The Column formatting options will always round up and I need them to truncate. What I'm looking for is whole numbers without rounding. The results I'm looking for is in the column labeled "Result". Is there a way of doing this without inserting a column using the "TRUNC" command and then cut the results out and paste them back into the original column and deleting the inserted column. I can't leave the inserted column in my worksheet. The Insert Column, TRUNC Formula, CUT, & Paste takes a LONG Time but gets the desired results just looking for an easier way.

Data	Desired Result	Result With Formatting
0.75​	0​	1​
1.5​	1​	2​
0​	0​	0​
3​	3​	3​
2.7​	2​	3​
5​	5​	5​
9.6857​	9​	10​
257.9852​	257​	258​

Any Assistance would be appreciated.

 

[Joe4]

Is there a way of doing this without inserting a column using the "TRUNC" command and then cut the results out and paste them back into the original column and deleting the inserted column.

You could create a VBA Macro to do that all for you.

Or, you could use something like this, found in the Kutools utility: How to remove digits after decimal in Excel?
(see the "Remove Digits After Decimal Without Formula By Round Utility" section).

​

 

[Fluff]

How about

Sub JWGoldfinch()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "="""","""",int(" & .Address & "))")
   End With
End Sub

 

[Rick Rothstein]

While Fluff's code will produce the display you want in the cells, it will also physically change the value of the cell to what is displayed. If you need to keep the original value of the cell and only change the display of it (which is what cell formatting does), then you can use this macro instead...

Sub JWGoldfinch2()
  Dim Cell As Range
  For Each Cell In Range("C2", Range("C" & Rows.Count).End(xlUp))
    Cell.NumberFormat = """" & CStr(Int(Cell.Value)) & """"
  Next
End Sub