0

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
sku144
  • 119
  • 1
  • 2
  • 10

4 Answers4

11

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.

enter image description here

Choose Delimited and click Next.

enter image description here

Uncheck all the delimiters and click Next.

enter image description here

Select the text option and press Finish

:enter image description here

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.

Yorki Bonilla
  • 535
  • 2
  • 9
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.
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • This is not what is asked? Also it there is not way to refresh the cell without VBA? The other solution is that change the format of that cell before inserting the value. Format the cell with: `0.#0'` and put the number of hash `#` depending on the number you want after decimal, if you want 10 decimal number then put 10 # to it – nishit dey Nov 23 '17 at 13:02
  • Unfortunately this won't help because I open XML files via excel and it parses these files and shows them on a new workbook with default cells format – sku144 Nov 23 '17 at 15:26
  • @sku144 - my understanding is that before the first point you fix the format the way you want it to. – Vityata Nov 23 '17 at 15:28
1
  • 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.

Lisa
  • 532
  • 1
  • 6
  • 17
0
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
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Ethel Aardvaark
  • 1