I find myself converting formulas to text frequently so I can copy/paste
them without the forumlas updating, then formatting back to general.

When I do this, the value still shows the text format until I open and
close(double click cell- press enter) each cell. How do I get the
values to show up without that?

TIA


--
iturnrocks
------------------------------------------------------------------------
iturnrocks's Profile: http://www.excelforum.com/member.php...o&userid=37246
View this thread: http://www.excelforum.com/showthread...hreadid=570790

Two techniques:

1) Use macros to do the conversion: save these in your personal.xls, add two custom buttons to your
commandbar to fire them, then select the cell(s) that you want to convert and run them.

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub


2) Use replace to find and replace = with XXXXX, then the reverse to switch back.

HTH,
Bernie
MS Excel MVP


"iturnrocks" wrote in message
...

I find myself converting formulas to text frequently so I can copy/paste
them without the forumlas updating, then formatting back to general.

When I do this, the value still shows the text format until I open and
close(double click cell- press enter) each cell. How do I get the
values to show up without that?

TIA


--
iturnrocks
------------------------------------------------------------------------
iturnrocks's Profile: http://www.excelforum.com/member.php...o&userid=37246
View this thread: http://www.excelforum.com/showthread...hreadid=570790

I guess I need to look into macros with as much time as I spend in
Excel. That looks a little over my head, but Ill learn it eventually.

Thank you


--
iturnrocks
------------------------------------------------------------------------
iturnrocks's Profile: http://www.excelforum.com/member.php...o&userid=37246
View this thread: http://www.excelforum.com/showthread...hreadid=570790

Or use Bernie's second suggestion.

iturnrocks wrote:

I guess I need to look into macros with as much time as I spend in
Excel. That looks a little over my head, but Ill learn it eventually.

Thank you

--
iturnrocks
------------------------------------------------------------------------
iturnrocks's Profile: http://www.excelforum.com/member.php...o&userid=37246
View this thread: http://www.excelforum.com/showthread...hreadid=570790

--

Dave Peterson