Format: General - Text - General

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

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(xlCellTypeFormulas)
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

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