why "formatting" is not auto-updated?

[Melissa]

Why is it that when i format some cells (containing numbers) as text, the
formatting is not applied immediately? I've had to "F2 + enter" each cell to
get the changes to take effect. What am I doing wrong?

[Dave Peterson]

Changing the format of a cell tells excel to change the way it's displayed--not
change the value of the cell.

To change the value of the cell, you need to reenter the value (F2|enter is
sufficient).

If you have lots to do, you could use a helper column:
=a1&""
drag down
convert to values (copy|paste special|values)
and delete the original column.

Or you could have a macro that does the work for you:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

Set myRng = Selection

For Each myCell In myRng.Cells
If myCell.HasFormula Then
'do nothing
Else
myCell.NumberFormat = "@"
myCell.Value = myCell.Value
End If
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Melissa wrote:
>
> Why is it that when i format some cells (containing numbers) as text, the
> formatting is not applied immediately? I've had to "F2 + enter" each cell to
> get the changes to take effect. What am I doing wrong?

--

Dave Peterson