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 -- iturnrocks ------------------------------------------------------------------------ iturnrocks's Profile: http://www.excelforum.com/member.php...o&userid=37246 View this thread: http://www.excelforum.com/showthread...hreadid=570790 |
Format: General - Text - General
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 |
Format: General - Text - General
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 |
Format: General - Text - General
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 |
All times are GMT +1. The time now is 09:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com