# Round numbers, then remove all decimals

#### Mark Green

##### Board Regular
I have a column of 170+ numbers that have different decimal places.
Some say, 297.4356, another 454.27, another 386.537 etc.
For each number in the column I would first like to round the number up or down,
THEN remove the decimals completely.

So that when you highlight a cell and look at the top bar you see only the integer, no decimal at all.

I hope someone can tell me how I can achieve this?

TIA

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Kenneth Hobson

##### Well-known Member
Code:
``````Sub Main()
With Selection
.NumberFormat = "#"
.Value = .Value
End With
End Sub``````

#### Macropod

##### Retired Moderator
In a spare column, insert:
=ROUND(A1,0)
where A1 is the reference to the first data cell you want to round.
Copy down to the end of your range.
Copy the range with the formula
Paste into the first data cell, using Paste Special>Values
Delete the formulae.

#### Kenneth Hobson

##### Well-known Member
Replace #2 wih:
Code:
``````Sub Main()
Dim c As Range
Selection.NumberFormat = "General"
For Each c In Selection
c.Value = Round(c.Value, 0)
Next c
End Sub``````

#### Mark Green

##### Board Regular
Thanks very much Kenneth and Paul!
Your solutions worked out nicely. Had to try them both.
Now I can compare a second column of numbers (integers) for any differences.

#### Macropod

##### Retired Moderator
If that's all you needed to do, you could have used a formula like:
=ROUND(A1,0)-B1
where A1 holds the raw number and B1 holds the supposedly-rounded one. Any non-zero results would represent material differences.

#### Mark Green

##### Board Regular
If that's all you needed to do, you could have used a formula like:
=ROUND(A1,0)-B1
where A1 holds the raw number and B1 holds the supposedly-rounded one. Any non-zero results would represent material differences.

Now that was really cool too! I just had to try it out also.
I would say that is the easiest method. I’ll file it away
if I need to compare numbers again in the future.
Thanks again!

#### Anand Sharma

##### Board Regular
You can use =int(a1), this also removes decimals from the number

#### Macropod

##### Retired Moderator
You can use =int(a1), this also removes decimals from the number
It does more than that: it rounds the number down; the OP just wanted them rounded, which is entirely different.

Replies
3
Views
841
Replies
15
Views
521
Replies
6
Views
201
Replies
3
Views
367
Replies
2
Views
283

1,191,036
Messages
5,984,273
Members
439,881
Latest member
Amitoj95

### 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?

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