Copy formulas as text

[MetLife]

Hi,

I have a column of formulas, and I want to copy the formula's text into another column in excel.

So I have something like

A1 = "=Vlookup(B3,...)"
A2 = "=Vlookup(B3,...)"
....
A100 = "=Vlookup..."

And I want to copy cells A1 through A100 to column C, such that it has the text only.

C1 = "Vlookup(B3,...)"
C2 = "Vlookup(B3,...)"
....
C100 = "Vlookup..."

How can I do this?

Thanks

 

[MetLife]

OK, I figured it out!

Just copy to Notepad and then back to excel.

Thanks

 

[JugglerJAF]

There is also a function in more recent versions of Excel (I think it's Excel 2013 and later) called FORMULATEXT

Typing =FORMULATEXT(A1) would return the formula contained in cell A1. This should save you a bit of messing about with Notepad!

 

[MetLife]

Actually this is not working because there are "=" signs in the formulas!

 

[Joe4]

If you do not have the newer versions of Excel that have the FormulaText function, you can create your own.

Here is a real short User Defined Function that will do what you want. Just place it in a General/Standard module, and then use like any other function in your workbook.

Function TextOfFormula(cell As Range) As String
    
    If cell.Count > 1 Then
        TextOfFormula = "Too many cells"
    Else
        TextOfFormula = Format(cell.Formula, "@")
    End If
    
End Function

So, if you want to return the formula in cell E1, you work just use the function:
=TextOfFormula(E1)

 

[MetLife]

If you do not have the newer versions of Excel that have the FormulaText function, you can create your own.

Here is a real short User Defined Function that will do what you want. Just place it in a General/Standard module, and then use like any other function in your workbook.

Function TextOfFormula(cell As Range) As String
    
    If cell.Count > 1 Then
        TextOfFormula = "Too many cells"
    Else
        TextOfFormula = Format(cell.Formula, "@")
    End If
    
End Function

So, if you want to return the formula in cell E1, you work just use the function:
=TextOfFormula(E1)

Thanks!

 

[Joe4]

You are welcome!