Formatting a column without Rounding Up

JWGoldfinch

New Member
Joined
Dec 23, 2009
Messages
49
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.

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


 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,573
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub JWGoldfinch()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "="""","""",int(" & .Address & "))")
   End With
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,878
Office Version
  1. 2010
Platform
  1. Windows
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...
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,902
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top