Insert ' in front of formulas

jakobt

Active Member
Joined
May 31, 2010
Messages
337
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top