Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By Peter_SSs

Copy formula and paste as text

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 ...

  1. #1
    Board Regular
    Join Date
    Aug 2005
    Posts
    50

    Default Copy formula and paste as text

    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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,532

    Default Re: Copy formula and paste as text

    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

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    14,777

    Default Re: Copy formula and paste as text

    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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    14,777

    Default Re: Copy formula and paste as text

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

    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
    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.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,532

    Default Re: Copy formula and paste as text

    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.
    evergreen1 likes this.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  6. #6
    Board Regular
    Join Date
    Aug 2005
    Posts
    50

    Default Re: Copy formula and paste as text

    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

  7. #7
    New Member
    Join Date
    Mar 2014
    Posts
    44

    Default Re: Copy formula and paste as text

    Quote Originally Posted by Peter_SSs View Post
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com