Replace Special Characters in Selection

[ajhez]

Hi all

I'm trying to remove special characters within a selection in Excel (special characters being anything that cannot be used in a file name for saving purposes - e.g. "`", "!", "@", "#", "$", ";", "^", "(", ")", "_", "-", "=", "+", _
"{", "[", "}", "]", "\", "|", ";", ":", "'", """", ",", "<", ".", ">", "/", "?".

I'm using the below, but can only get it to work for "/" how do I expand fnd to encompass as many of the special characters as possible?

I need to retain it to work based on my selection rather than a fixed group of cells/columns/rows.


Sub FindandReplace()
Dim fnd As Variant
Dim rplc As Variant

fnd = "/"
rplc = ""

Selection.Replace What:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End Sub

Thanks,
AJHEZ

[snb]

sub M_snb()
for j=1 to 24
Selection.Replace mid("{}[])(*&^%$#@!~`:";'<>,.",j,1),""
next
End Sub

[Paul_Hossler]

Not sure about using Selection, but maybe something like this



Option Explicit
Sub drv()
Dim s As String

s = "asdf1234#$%^&asdasdf[]"
'two ways to call
Call NoBadChar(s)
' NoBadChar s

MsgBox s
End Sub

Sub NoBadChar(ByRef s As String)
Const cNotAllowed As String = "{}[])(*&^%$#@!~`:""; '<>,." ' note doubled quote inside

Dim i As Long

For i = 1 To Len(cNotAllowed)
s = Replace(s, Mid(cNotAllowed, i, 1), vbNullString)
Next
End Sub

[Fennek]

Hello,

an alternative:



Sub Fen()
Tx = Cells(1, 1)
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
Debug.Print .Replace(Tx, "")
End With
End Sub

Function iFen(ByVal rng As Range) As String

With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
iFen = .Replace(rng.Value, "")
End With
End Function


regards