Multiple Find & Replace

K

kevin

I have an excel file that I need to remove some characters from
selection like '?)(*&^%'. Is there a way of doing this in one go, or d
I have to do it one at a time? e.g. find '?' replace with ''

I will have to do this across many spreadsheets, so if there is a wa
of doing it quicker, I would appreciate being pointed in the righ
direction
 
D

Dave O

For most of those characters you could record a macro that replaces &
with "". However, the star * and question mark ? create a problem
because Excel recognizes those as a wildcard, and if you instruct
Excel to replace them with "" then every character will be replaced,
leaving you with a blank spreadsheet.

The workaround for that is to write some VBA code that translates each
character into its ASCII code equivalent. That way you can write the
code to read each cell and replace each ASCII character 42 (the star
symbol) with ASCII char 0 (a null character).
 
G

Gord Dibben

Kevin

This macro will strip out all but numbers or text from selected cells.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rcell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rcell In rConsts
With rcell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rcell
End If
End Sub

If unfamiliar with VBA and macros, visit David McRitchie's "getting started
with VBA and macros" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
D

Dave Peterson

If you use ~*, ~? and ~~ (for a single ~), you can use the mass change (either
from the worksheet interface or from VBA).
 
Top