Round numbers, then remove all decimals

[Mark Green]

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

 

[Kenneth Hobson]

Sub Main()
  With Selection
    .NumberFormat = "#"
    .Value = .Value
  End With
End Sub

 

[Macropod]

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]

Replace #2 wih:

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]

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]

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]

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]

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

 

[Macropod]

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.