Excel - Changing cell formula to text

September 2017



Issue



Consider that I have:
  • In cell A4, it contains a formula =Sum(A1:B3)+A3/B2.
  • How to extract this formula in cell A6 as a string of text?
    • That is ... I want cell A6 to display =Sum(A1:B3)+A3/B2
    • Using Control + ~ I can view this formula in a spreadsheet.
    • But it is in view only mode. Pressing Control + ~ will turn back to normal.
    • Also, I've tried to put ' at the front and paste the formula in one cell ... but this is not applicable in VBA (marco writing).
    • VBA will capture the script as below

ActiveCell.FormulaR1C1 = "'=SUM(A1:B3)+A3/B2"  



*
    • Note that the formula in cell 6 may be =A3-B2+A1 (not =SUM(A1:B3)+A3/B2)
    • Using Marcos to run it will overwrite the new formula to =SUM(A1:B3)+A3/B2 instead of =A3-B2+A1
    • How to use the functions within Excel or use Marcos to accomplish changing cell formula to text.

Solution


Try with this macro :

Sub DysplayFormula() 
    Range("A6").Value = "'" & Range("A4").Formula & "" 
End Sub 

Notes


Thanks to aquarelle,for this tip.

Related

Published by aakai1056. Latest update on March 22, 2012 at 10:43 AM by aakai1056.
This document, titled "Excel - Changing cell formula to text," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).