Excel

Remove Apostrophes

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Zack Barresse

Description:

Removes apostrophes as the first character in your entire active sheet. 

Discussion:

Sometimes on an import or query (e.g., from Access) you get text into Excel with an apostrophe in front of it, which is viewable only in the formula bar. Excel *ignores* showing this apostrophe in the cell but automatically shows the cell as text. Formulas with a leading apostrophe (seen as text) will now evaluate as entered. 

Code:

instructions for use 

			
Option Explicit 
Sub DeathToApostrophe() 
    Dim s As Range, temp As String 
    If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _ 
    vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub 
    Application.ScreenUpdating = False 
    For Each s In ActiveSheet.UsedRange 
        If s.HasFormula = False Then 
             'Gets text and rewrites to same cell without   the apostrophe.
            s.Value = s.Text 
        End If 
    Next s 
    Application.ScreenUpdating = True 
End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Right-click desired file on left.
  4. Choose Insert -Module.
  5. Paste code into the right pane.
  6. Press Alt + Q to close the VBE.
  7. Save workbook before any other changes.
 

Test the code:

  1. Ensure Active sheet is the desired sheet.
  2. Press Alt + F8.
  3. Choose 'DeathToApostrophe'.
  4. Press 'Run'.
  5. Confirm with 'Ok' to run.
 

Sample File:

RemoveApos.zip 6.16KB 

Approved by mdmackillop


This entry has been viewed 347 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express