EXCEL > how to refresh a cell to apply new cell format?

Question

I have a value in a cell, say 123456789.12345. By default excel shows this as 123456789.1. I need to see all digits after comma, so I change cell format to custom > @

To apply this change I need to refresh the cell, for example select it and press F2, then Enter. But how to apply this refresh for a column or a range?

No VBA if possible please.

Answer 1

You can speed up the process doing this:

NO VBA OPTION

Select the column and go to the Data ribbon and click Text to Columns.

Choose Delimited and click Next.

Uncheck all the delimiters and click Next.

Select the text option and press Finish

:

The number formatting of all the cells will update.

This trick is a bit of a hack, but it works. What it does is take all the values from each row and then re-enters them into the cells automatically. For this reason, this trick will not work for cells that are formulas. If you have formulas, pressing F9 should recalculate the sheet and update the number format. But in my experience, I haven’t had this problem with formulas.

VBA OPTION

Just create a VBA with this code:

  Sub Oval1_Click()
    Dim i As Integer
    i = 1
    Do Until Cells(i, 1) = 0
     Cells(i, 1).NumberFormat = "@"
     Cells(i, 1).Select
     SendKeys "{F2}", True
     SendKeys "{ENTER}", True
     i = i + 1
   Loop
 End Sub

Have in mind that for this to work, you will need a "button" with the name Oval1_click, or just add the code to the button you create.

Explain:!

1: First we start the code on button click

  Sub Oval1_Click()

Then we declare a variable that is going to be used as the incremental value for the cell row.

    Dim i As Integer
    i = 1

We start the loop in the row 1, column 1 (a) that translate in A1, only if the value is not equal to 0.

    Do Until Cells(i, 1) = 0

We assign the @ format to the selected row (A1)

     Cells(i, 1).NumberFormat = "@"

We select, press F2, and press Enter, to that row (A1)

     Cells(i, 1).Select
     SendKeys "{F2}", True
     SendKeys "{ENTER}", True

We increment i value so it can change rows

     i = i + 1

We finish the loop and the code if we get to an empty cell in that column

   Loop
 End Sub

And thats it.

Answer 2

• Format the first cell the way you wanted.
• Copy the cell with the correct format.
• Select the other cells.
• Right mouse button -> Paste Special (or Ctrl+Alt+ V).
• Select the Format RadioBox.
• Click Ok button.
• Press Enter.

Answer 3

• Select the cells in one column and set the new format
• Go to Data --> Text To Columns and press Finish

That should refresh the selected cells and apply the new format.

Answer 4

Sub ForceAsText()

    Dim cel As Range
    Dim selectedRange As Range

    Set selectedRange = Application.Selection

    For Each MyCell In selectedRange.Cells
        MyCell.NumberFormat = "@"
        If MyCell.HasFormula Then
            Dim Val As String
            Val = MyCell.Formula
            MyCell.Value = Val
        End If
    Next MyCell

End Sub