Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Insert ' in front of formulas

  1. #1
    Board Regular
    Join Date
    May 2010
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert ' in front of formulas

    For a selection of cells, want to insert a ' in front of formulas in each cell.

    Additionally I would like another macro to remove ' from all selected cells. Or remove first character.

  2. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,422
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert ' in front of formulas

    You do realize that by doing that, your formulas will no longer work?

    You could record a macro to do what you want, ten jut use it whenever you need to?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    Board Regular
    Join Date
    Dec 2013
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert ' in front of formulas

    If you have 2013 or later you could use FORMULATEXT function instead of inserting the ' in each cell
    (you would of course have to put that function in another column and copy-paste the result)

    Sorry I can't post an example - only had excel 2013 in my last job .. new job only has 2010

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,422
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert ' in front of formulas

    Another option might be to use "Show Formulas". Find it on the Formula tab/Formula Auditing/. This will show all formulas in your sheet - until you "turn it off"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,004
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Insert ' in front of formulas

    Quote Originally Posted by jakobt View Post
    For a selection of cells, want to insert a ' in front of formulas in each cell.

    Additionally I would like another macro to remove ' from all selected cells. Or remove first character.
    These two macros should do what you asked for...
    Code:
    Sub InsertApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) = 0 Then
          If Cell.HasFormula Then Cell.Value = "'" & Cell.Formula
        End If
      Next
    End Sub
    
    Sub RemoveApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) > 0 Then
          Cell.Formula = Cell.Formula
          If IsError(Cell.Value) Then Cell.FormulaArray = Cell.FormulaArray
        End If
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,004
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Insert ' in front of formulas

    Quote Originally Posted by Rick Rothstein View Post
    These two macros should do what you asked for...
    Code:
    Sub InsertApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) = 0 Then
          If Cell.HasFormula Then Cell.Value = "'" & Cell.Formula
        End If
      Next
    End Sub
    
    Sub RemoveApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) > 0 Then
          Cell.Formula = Cell.Formula
          If IsError(Cell.Value) Then Cell.FormulaArray = Cell.FormulaArray
        End If
      Next
    End Sub
    The above code does not handle array-entered formulas correctly... I believe the following code does.
    Code:
    Sub InsertApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) = 0 Then
          If Cell.HasArray Then
            Cell.Value = Replace(Cell.Formula, "=", "'{=") & "}"
          ElseIf Cell.HasFormula Then
            Cell.Value = "'" & Cell.Formula
          End If
        End If
      Next
    End Sub
    
    Sub RemoveApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) > 0 Then
          If Left(Cell.Value, 2) = "{=" Then
            Cell.FormulaArray = "=" & Replace(Mid(Cell.Value, 3), "}", "")
          Else
            Cell.Formula = Cell.Formula
          End If
        End If
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    May 2010
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert ' in front of formulas

    Rick thanks. Very nice advisory work.
    I don't have any arrays so this is not an issue.
    Lets Kis. Keep it simple.
    One extra functionality required:
    ' should not just be in front of formulas it should also be in front of pure numbers or text. Ie. '50 or 'Text

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,004
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Insert ' in front of formulas

    Quote Originally Posted by jakobt View Post
    One extra functionality required:
    ' should not just be in front of formulas it should also be in front of pure numbers or text. Ie. '50 or 'Text
    I coded it for formulas only because your opening message said "For a selection of cells, want to insert a ' in front of formulas in each cell." Handling non-formulas appears to be a whole other task because the apostrophe seems to be harder to find and eliminate for constants. I'll look at this problem a little bit later and get back to you then.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,004
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Insert ' in front of formulas

    Quote Originally Posted by jakobt View Post
    One extra functionality required:
    ' should not just be in front of formulas it should also be in front of pure numbers or text. Ie. '50 or 'Text
    A follow-up to the comment I just made in Message #8... I can do what you asked for numbers, that does not appear to be a problem, but why do you want to put an apostrophe in front of text values? Nothing visibly changes when you do that and the apostrophe appears to be impossible to remove once placed there.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,004
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Insert ' in front of formulas

    Quote Originally Posted by Rick Rothstein View Post
    A follow-up to the comment I just made in Message #8... I can do what you asked for numbers, that does not appear to be a problem, but why do you want to put an apostrophe in front of text values? Nothing visibly changes when you do that and the apostrophe appears to be impossible to remove once placed there.
    Okay, I figured it out. These two macros will do what you have now asked for...
    Code:
    Sub InsertApostrophe()
      Dim Cell As Range
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) = 0 Then
          If Cell.HasArray Then
            Cell.Value = Replace(Cell.Formula, "=", "'{=") & "}"
          Else
            Cell.Value = "'" & Cell.Formula
          End If
        End If
      Next
    End Sub
    
    Sub RemoveApostrophe()
      Dim Cell As Range, Text As String
      For Each Cell In Selection
        If Len(Cell.PrefixCharacter) > 0 Then
          If Left(Cell.Value, 2) = "{=" Then
            Cell.FormulaArray = "=" & Replace(Mid(Cell.Value, 3), "}", "")
          Else
            Cell.Formula = Cell.Formula
          End If
          If Len(Cell.PrefixCharacter) > 0 Then
            Text = Cell.Value
            Cell.Clear
            Cell.Value = Text
          End If
        End If
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

User Tag List

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