How to Remove Characters in a Cell on Excel 2007 With VBA
Microsoft's Visual Basic for Applications (VBA) is a subset of Visual Basic that is used to customize Microsoft Office applications. A piece of VBA code, a macro, can replace many repetitive functions like find and replace. If you want to remove certain characters from cells, like periods or commas, write a routine to perform the task for you. Instead of having to click "Find and Replace" multiple times, you can get VBA to perform the task for you in one button push.
Other People Are Reading
Instructions
-
-
1
Press "Alt" + "F11" in Excel to open the Visual Basic Editor (VBE).
-
2
Click "Insert" > "Module."
-
-
3
Cut and paste the following code into the blank window:
Option Explicit
Sub Remove()
Dim rgxRegExp As Object
Dim rngCell As Range, rngRange As Range
Set rngRange = Sheet1.Range("A1:A3")
Set rgxRegExp = CreateObject("VBScript.RegExp")
rgxRegExp.Global = True
rgxRegExp.Pattern = "\.|,"
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
For Each rngCell In rngRange.SpecialCells(xlCellTypeConstants)
rngCell.Value = rgxRegExp.Replace(rngCell.Value, vbNullString)
Next
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
-
4
Change the range to match the range of data that you want to remove characters from. For example, if you want to remove characters from cells D1 to D110 in worksheet 2, change line 5 in the code to read:
Set rngRange = Sheet2.Range("D1:D110").
-
5
Change the code to reflect the characters you want removed from the cells in your worksheet. The code as written removes periods or commas (rgxRegExp.Pattern = "\.|,"). You could change to code that removes commas and hyphens (rgxRegExp.Pattern = "\,|-") or periods and percentages (rgxRegExp.Pattern = "\.|,"), or you could replace numbers and other special characters.
-
6
Press "F5" to run the routine.
-
1
Tips & Warnings
You don't need to exit the VBE to check whether the code has performed the action you expected. Toggle between the worksheet and the VBA by pressing "Alt" + "F11."
