Copy formula and paste as text

[meileetan]

I have 10,000+ rows of data.
I have a formula in 1 column. Is there a way to copy the text of the formula and paste as plain text? e.g. my formula is =IF(H3>0,INDEX(WebiFields,Comparison!H8154),IFERROR(VLOOKUP(G3,$G$2:$K2,4,0),"?"))

I don't want to see the results of the formula in my worksheet, just the text after the = sign.

 

[Peter_SSs]

Would Excel's built-in option to display formulas instead of their results be any use for what you are trying to do? (keyboard shortcut: Ctrl+`)

 

[Rick Rothstein]

If Column A's formulas were generated by copying down the first formula in the column, then give this macro a try...

Sub CopyFormulaText()
  Dim UnusedColumn As Long, LastRow As Long, FirstFormula As String, LeadingCharacters As String
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  FirstFormula = Columns("A").SpecialCells(xlCellTypeFormulas)(1).Formula
  LeadingCharacters = Left(FirstFormula, 3)
  With Cells(1, UnusedColumn).Resize(LastRow)
    .Value = Range("A1:A" & LastRow).Formula
    .SpecialCells(xlCellTypeConstants).Clear
    .Replace LeadingCharacters, Mid(LeadingCharacters, 2), xlPart
  End With
End Sub

 

[Rick Rothstein]

Okay, here is the "generalized" version of the macro I just posted...

Sub CopyFormulaText()
  Dim UnusedColumn As Long, LastRow As Long, FirstFormula As String, LeadingCharacters As String
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(1, UnusedColumn).Resize(LastRow)
    .Value = Range("A1:A" & LastRow).Formula
    .SpecialCells(xlCellTypeConstants).Clear
  End With
  On Error GoTo Done
  Do
    FirstFormula = Columns(UnusedColumn).SpecialCells(xlCellTypeFormulas)(1).Formula
    LeadingCharacters = Left(FirstFormula, 3)
    Cells(1, UnusedColumn).Resize(LastRow).Replace LeadingCharacters, Mid(LeadingCharacters, 2), xlPart
  Loop
Done:
  Application.ScreenUpdating = True
End Sub

With this code, it does not matter how many different formulas you have in Column A. Note that with 10,000+ rows to process, the code will proabably take awhile to finish executing.

 

[Peter_SSs]

Depends what the purpose really is here, but another option that may suffice is to ..

1. Select the column by clicking its heading label.
2. Edit/Replace...|Find what: = |Replace with: type a space followed by = |In Options>> ensure that 'Match entire cell contents' is not checked|Replace All

It is possible that your formulas may contain more than one = sign and this process will insert a space before all the = signs. However, if you are just doing this to view your formulas then that most likely will not be a problem. The process is easily reversed by doing the Replace the other way around.

 

[meileetan]

Thanks Peter. I'll try that.
In the meantime, I found that the Convert Text function works very well for what I'm trying to do.

 

[evergreen1]

Depends what the purpose really is here, but another option that may suffice is to ..

1. Select the column by clicking its heading label.
2. Edit/Replace...|Find what: = |Replace with: type a space followed by = |In Options>> ensure that 'Match entire cell contents' is not checked|Replace All

It is possible that your formulas may contain more than one = sign and this process will insert a space before all the = signs. However, if you are just doing this to view your formulas then that most likely will not be a problem. The process is easily reversed by doing the Replace the other way around.

thanks peter for the suggestion. i was looking something like this. have a nice day

 

[Sathya_73]

In Excel 2013
FORMULATEXT function serves your query
(eg.) =FORMULATEXT(c4)
Thanks

 

[IanBurrell]

So I tend to use a variation of an idea stated above.

[1] convert the fomulaes to text - copy out:

> Save workbook.
> Select column / row with formulas
> Cntrl + H (find and replace)
> Find =' which is the precursor to formulas
> Replace with **='

This then converts the formulae in the column to text - with a double asterix precursor

[2] convert the text back to formulaes:

> Copy the row / column into another adjacent row / column
> With original row / column - reverse the copy / replace
>> Select column / row with formulas
>> Cntrl + H (find and replace)
>> Find **=' which is the new precursor to formulas
>> Replace with ='to convert it back to formulas


Hope it helps - have fun
IDB

 

[pmich]

Thanks Sathya_73.
Works fine.