Removing Special Characters

[ajhez]

Hi all,

I have a column in excel that contains text which I want to use in the file name / file path when my code automatically saves the documents it generates. The process will create approximately 900 documents (1 document for each cell in my text column).

My problem is that I have noticed an error on on those documents where the text cell contains "/" because this is not a permitted character for the file path when saving documents. I have created the below code to remove the "/" from the cells I select, but i wondered if there was a way to remove all special characters OR to take my existing code and to make it search & replace for more than just "/" but for example to also include "?" "!" "*" for example.

Sub FindandReplace()

Dim fnd As Variant
Dim rplc As Variant

fnd = "/"
rplc = ""

For Each c In Selection
c.Replace What:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next
End Sub


Thanks!

[Paul_Hossler]

From Help



In the Find what box, type the text or numbers that you want to
search for, or click the arrow in the Find what box, and then
click a recent search in the list.
You can use wildcard characters, such as an asterisk (*) or a question mark
(?), in your search criteria:


Use the asterisk to find any string of characters. For example, s*d finds "sad" and "started".

Use the question mark to find any single character. For example, s?t finds "sat" and "set".

Tip You can find asterisks, question marks, and tilde
characters (~) in worksheet data by preceding them with a tilde character in the
Find what box. For example, to find data that contains "?", you
would type ~? as your search criteria.




You avoid looping this way



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

[mancubus]

a udf will be helpful if it's a repeated task.





Function CleanText(SpecChars As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\\|/|:|\~|\*|""|\?|<|>|\||{|}|\[|\]"
CleanText = .Replace(SpecChars, "")
End With
End Function


| means OR
\ means treat RegEx special character as normal character. (\\ is for \ and \| is for |)


then


Sub FindandReplace()

Dim c As Range

For Each c In Range("A2:A5000")
c.Value = CleanText(c.Value)
Next

End Sub

[ajhez]

Thanks both!

Great to have both suggestions as could be simplest just to opt for the Find What box, but also I like the idea of the macro to tackle a bigger job.

Excellent advice as usual on here. Thanks again!