Reducing Number of Decimals

[Rupert Bennett]

I have a set of numbers in a range that are formatted to display two decimal places. However, I have a problem when I create a CSV file from this data. The underlying data is something like
136.37894
121.20538
101.95438
When I create the CSV file, I get all these numbers, but I only want two decimal places, rounded.

Can anyone help with this? I have been searching for hours, without success.

TIA,
Rupert

 

[NeonRedSharpie]

=ROUND(A1,2)

Where A1 is your data. Then just paste as values to trim off the extra parts.

 

[Rupert Bennett]

Thanks for your quick reply. However, this is not what I am looking for. My worksheet is already formatted and is displaying the values with two decimal places. The problem I am having is when I run a VBA script on this worksheet to produce a CSV file, the CSV file now displays all the numbers. I was hoping to get some VBA to give me a CSV output with two decimal places. I hope this explains it more clearly. Thanks.

 

[shg]

Either add ROUND to each of the formulas, or select the option Precision as Displayed.

If you consider the latter, be aware that every calculation will use the results that appear in cells, ignoring the extended precision that is available. For example, if a cell contains =1/3, and it's displayed as 0.33, it's the 0.33 that gets used. So it you multiply it by 100, the result will be 33.00. It's something you need to be extremely careful about.

 

[joeu2004]

My worksheet is already formatted and is displaying the values with two decimal places. The problem I am having is when I run a VBA script on this worksheet to produce a CSV file, the CSV file now displays all the numbers. I was hoping to get some VBA to give me a CSV output with two decimal places.

I suggest that you provide the "VBA script" (macro?) that produces the CSV file. Ideally, upload an example Excel file to a file-sharing website and post the "shared" (public) URL in a response here. Alternatively, at the very least, copy the "VBA script" into a response, surrounded by CODE tags. (Click on the "#" icon in the toolbar.)

If the worksheet is "already formatted" to display two decimal places and you use the ActiveWorkbook.SaveAs method to create the CSV file, the numbers should appear with only two decimal places in the CSV file, even if their actual value is more precise.

So the devil is in the details.

Wild guess.... Perhaps you can use WorksheetFunction.Round(...,2) if you write to the CSV file "manually" (e.g. using a Print# statement). Use that function instead of VBA Round(...,2). The latter rounds differently than Excel in some cases.

 

[Rupert Bennett]

Here is a copy of my code. I hope this helps. Thanks to all for tyring to help.
code:
Sub CreateCSVFile()
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Dim MyPath As String, MyFile As String, ThisFile As String
'MyPath = "\\BI\whg_bi\datacollection\pmsdatafiles\FSPMS\Unprocessed" '.... Change when correct folder location is decided on.
MyPath = ThisWorkbook.Path '... This is used for testing
MyFile = Sheets("Segmentation RevPAR").Range("AI1").Value & PropNum & "Segmentation RevPAR.txt"
ThisFile = MyPath & Application.PathSeparator & MyFile

'Delete Prior copy of the file
On Error Resume Next
Kill (ThisFile)
On Error GoTo 0

'Open the file
Open ThisFile For Output As #1
intLastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

'Write out the CSV file
For j = 1 To intLastRow
Print #1, Cells(j, 1).Value & "," & Cells(j, 2).Value & "," & Cells(j, 3).Value & "," & Cells(j, 4).Value & "," & Cells(j, 5).Value & "," _
& Cells(j, 6).Value & "," & Cells(j, 7).Value & "," & Cells(j, 8).Value & "," & Cells(j, 9).Value & "," & Cells(j, 10).Value & "," _
& Cells(j, 11).Value & "," & Cells(j, 12).Value & "," & Cells(j, 13).Value & "," & Cells(j, 14).Value & "," & Cells(j, 15).Value & "," _
& Cells(j, 16).Value & "," & Cells(j, 17).Value & "," & Cells(j, 18).Value & "," & Cells(j, 19).Value & "," & Cells(j, 20).Value & "," _
& Cells(j, 21).Value & "," & Cells(j, 22).Value & "," & Cells(j, 23).Value & "," & Cells(j, 24).Value & "," & Cells(j, 25).Value & "," _
& Cells(j, 26).Value & "," & Cells(j, 27).Value

Next j
Close #1

MsgBox "The file " & MyFile & " was successfully created and placed in the assigned folder."

End Sub

Rupert

 

[Rupert Bennett]

Sorry ... It seems I have not gotten the hang of using code tags.

 

[joeu2004]

 'Write out the CSV file
For j = 1 To intLastRow
 Print #1, Cells(j, 1).Value & "," & Cells(j, 2).Value & "," & Cells(j, 3).Value & "," & Cells(j, 4).Value & "," & Cells(j, 5).Value & "," _
         & Cells(j, 6).Value & "," & Cells(j, 7).Value & "," & Cells(j, 8).Value & "," & Cells(j, 9).Value & "," & Cells(j, 10).Value & "," _
         & Cells(j, 11).Value & "," & Cells(j, 12).Value & "," & Cells(j, 13).Value & "," & Cells(j, 14).Value & "," & Cells(j, 15).Value & "," _
         & Cells(j, 16).Value & "," & Cells(j, 17).Value & "," & Cells(j, 18).Value & "," & Cells(j, 19).Value & "," & Cells(j, 20).Value & "," _
         & Cells(j, 21).Value & "," & Cells(j, 22).Value & "," & Cells(j, 23).Value & "," & Cells(j, 24).Value & "," & Cells(j, 25).Value & "," _
         & Cells(j, 26).Value & "," & Cells(j, 27).Value
Next j

Try replacing Cells(j, 1).Value with Cells(j, 1).Text, assuming the cell is formatted as you want it to appear in the CSV file.

Alternatively, replace Cells(j, 1).Value with WorksheetFunction.Round(Cells(j, 1),2).

Of course, apply the same solution to all numeric cell references in the Print# statement.

[EDIT] Simplification.... I have not really studied your code, but I think the following might work for you (untested):

Dim s As String, i As Long
[....]
For j = 1 To intLastRow
    s = ""
    For i = 1 to 27
        s = s & "," & Cells(j,i).Text
    Next i
    Print #1, Mid(s, 2)    ' skip first comma
Next J

 

[Rupert Bennett]

Thank you very much, joeu2004. Solution 1 worked perfectly. I have no doubt the simplified code will also work, but have not tried it as yet. Thanks again and have a great day.