Format: General - Text - General

[iturnrocks]

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

 

[Bernie Deitrick]

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]

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

Thank yo

 

[Dave Peterson]

Or use Bernie's second suggestion.

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