convert number stored as text to number without rounding the numbers

[star_saty]

Hi All,

I am looking for a VBA code for the below issue, Please help.

I am stuck where i have to convert the number which are stored as text and i have to convert them in number format and it should not round up the numbers.
Below is the sample data for your reference. once data gets convert to number it should look like as it is but it should be converted into number format.

In Excel When number are in text format they are aligned to Left but once it get converted to number format they are align to Right

See below screencap of the same.

Text Format Data
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]1.323551[/TD]
[TD]90.92[/TD]
[TD]6604.38[/TD]
[/TR]
[TR]
[TD]0.570486[/TD]
[TD]3.17[/TD]
[TD]2505.14[/TD]
[/TR]
[TR]
[TD]0.894552[/TD]
[TD]7.25[/TD]
[TD]13438.28[/TD]
[/TR]
</tbody>[/TABLE]


After converting above to number format it should look like below
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD="align: right"]1.323551[/TD]
[TD="align: right"]90.92[/TD]
[TD="align: right"]6604.38[/TD]
[/TR]
[TR]
[TD="align: right"]0.570486[/TD]
[TD="align: right"]3.17[/TD]
[TD="align: right"]2505.14[/TD]
[/TR]
[TR]
[TD="align: right"]0.894552[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]13438.28[/TD]
[/TR]
</tbody>[/TABLE]



Your help will be highly appreciated.
Thanks in advance

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried Data|Text To Columns|Finish? The macro recorder will give you the VBA code.

 

[star_saty]

Hi Andrew,

I am using below code to do the task but it roundup the number. Below code roundup the number to 1 digit.

sub report()
Range("A2:A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Call Number
End sub

Sub number()
Range("A2:A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Select
With Selection
Selection.NumberFormat = "0"
.Value = .Value
End With
End Sub

 

[Andrew Poulsom]

The code doesn't do any rounding. But this line formats the numbers with zero decimal places:

Selection.NumberFormat = "0"

Try using the General number format.