Turn decimals number into integer

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Hi, i would like to create a vba code so that to turn decimals into integer number for col. "K" and "N".

e.g.: 0.5 convert as 5.0
0.2 convert as 2.0

Thanking you in advance
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
Hi, i would like to create a vba code so that to turn decimals into integer number for col. "K" and "N".

e.g.: 0.5 convert as 5.0
0.2 convert as 2.0
Are the only possible cell values single decimal place numbers as shown in your two examples? If not, what should happen with values like 0.123 or 12.3456?
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Hi Rick, I am sorry that i done a fault. In these columns (K and N) the most cells numbers are with single decimal but there is also an integer too, like 1.0 (format). Regarding your above examples the numbers should convert as follow:
0.123 should be 1.2
12.3456 should be 12.3

Many thanks for your support
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
Hi Rick, I am sorry that i done a fault. In these columns (K and N) the most cells numbers are with single decimal but there is also an integer too, like 1.0 (format). Regarding your above examples the numbers should convert as follow:
0.123 should be 1.2
12.3456 should be 12.3

Many thanks for your support
So once you are done with the conversion, 0.123 and 1.23 will both become 1.2 ?
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
I am a little bit confused now and so i write all the circumstances.

0.0025 should be 0.2
0.0456 should be 4.6
0.1864 should be 18.6
2.4568 should be 245.7

Thanks once again
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
I am a little bit confused now and so i write all the circumstances.

0.0025 should be 0.2
0.0456 should be 4.6
0.1864 should be 18.6
2.4568 should be 245.7
Okay, that makes more sense although I think you have a mistake on the first one as you did not round it like you did the other examples... I'll assume it should have been rounded up. Here is a macro that should do what you are indicating you want...
Code:
[table="width: 500"]
[tr]
	[td]Sub MultiplyAllValuesInColumnsKandNby100()
  Dim Col As Variant
  For Each Col In Array("K", "N")
    With Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp))
      .Value = Evaluate(Replace("IF(ISNUMBER(@),ROUND(100*@,1),IF(@="""","""",@))", "@", .Address))
    End With
  Next
End Sub[/td]
[/tr]
[/table]
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Many thanks Rick. It works perfect and appears the result that i was required. I express my apologies, that i did not explain exactly, how should be the expected result on my first post. Thanks once again for your continues support. Hv a great day
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,048
Messages
5,466,249
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top