CODEWITHSUNDEEP
excelcustom-formatting
user253780
user253780

Reputation: 104

Excel - Display only the integer as though FLOOR was used, but keep decimal data for other formulas

I have a collection of values that almost never are complete whole numbers, but instead have the tenths place to some degree (i.e. 1000.8, 2049.3, etc). When displaying these values, I want Excel (2010) to show only the completed integer values, but not round them up (default behavior, doesn't appear I can change it). I need those fractional values to be kept for other formulas in the workbook, but I just don't want them displayed. Here's an example:


       A            B               C           D
    --+---------------------------------------------------------------
    1 | True Val    Display Val     Formula     Calculated
    2 | 1000.9      1000            =B2*3       3002.7
    3 | 2049.3      2049            =B3*3       6147.9
    4 | 515433.5    515433          =B4*3       1546300.5

What I want to see in these cells is what I have shown in Column B above, but the actual data held in those cells is what I have shown in Column A, and the result of a calculation I expect to see is shown in Column D (using formulas from Column C).

I have searched for hours, but everything I come across just doesn't accomplish what I'm looking for.

Please understand that I am NOT trying to use the TRUNC() or ROUNDDOWN() formula functions on these cells, as this would remove the fractional data, and my formulas would be using incorrect data during calculations. This is a purely cosmetic format I am looking for, so I can see whole numbers that have been completed, without seeing the decimals, and no rounding to an invalid integer. Again, the proper solution does not have Cell B2 multiplied by 3 result in 3000.

The closest I have figured out is to have these mirrored in another column where I have rounded-down/truncated the data, but this creates an unnecessary column I wish to avoid if possible. If there is no way to do this via custom formatting, altering the rounding method used in the sheet, or some other way, then I will be forced to add another column just for this.

Upvotes: 1

Views: 579

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If the cells in question contain constants, (not formulas) and there are not a large number of these cells, then select the cells and run this macro:

Sub FakeFormatNoDots()
    Dim DQ As String, mesage As String
    Dim r As Range

    DQ = Chr(34)

    For Each r In Selection.Cells.SpecialCells(2)
        v = r.Text
        If InStr(1, v, ".") > 0 Then
            mesage = DQ & Split(v, ".")(0) & DQ
            r.NumberFormat = mesage & ";" & mesage & ";" & mesage & ";"
        End If
    Next r
End Sub

Before:

enter image description here

and after:

enter image description here

NOTES:

This is not a good general solution because there are a limited number of Custom Formats that Excel can handle.

If you change the values, you must:

  1. clear the formats
  2. re-run the macro.

Upvotes: 1

Related Questions