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+`)
This is a discussion on Copy formula and paste as text within the Excel Questions forums, part of the Question Forums category; I have 10,000+ rows of data. I have a formula in 1 column. Is there a way to copy the ...
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.
Thank you,
Melissa Tan
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+`)
Hope this helps, good luck.
Peter
Excel 2010 (mainly), 2016, 2013, 2007, 2003 - Windows 10, 7
- Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
- Read: Forum Rules & Forum Use Guidelines
If Column A's formulas were generated by copying down the first formula in the column, then give this macro a try...
Code: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
Okay, here is the "generalized" version of the macro I just posted...
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.Code: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
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.
Hope this helps, good luck.
Peter
Excel 2010 (mainly), 2016, 2013, 2007, 2003 - Windows 10, 7
- Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
- Read: Forum Rules & Forum Use Guidelines
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.
Thank you,
Melissa Tan
In Excel 2013
FORMULATEXT function serves your query
(eg.) =FORMULATEXT(c4)
Thanks
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
Like this thread? Share it with others