Remove special characters from text string.

[S Oberlander]

I have a macro that saves my excel file and names it based on the selected cell, however very often there are characters that cannot be part of a filename. I would like to simply remove those characters and proceed with saving. There may be slashes and/or colons and other special characters in the selected cell, however I do not know its location as it varies.
How can I do this?

Random example:
The selected cell contains "Sample 1/1/22: Ready"
I want my filename to contain "Sample 1122 Ready"
or perhaps replace special characters with underscore so I get "Sample 1_1_22_ Ready".

 

[lrobbo314]

Here is a function that will by default replace the invalid characters with zero length strings. It also has an optional second argument that will allow you to replace the invalid characters with whatever character you want.

2022 OTM Arrivals Mexicali Airport Weekly (version 1).xlsb
	A	B
35	Sample 1/1/22: Ready	Sample 1122 Ready
36	Sample 1/1/22: Ready	Sample 1_1_22_ Ready
Sheet4

Cell Formulas
Range	Formula
B35	B35	=NoSpec(A35)
B36	B36	=NoSpec(A36,"_")

Function NoSpec(s As String, Optional r As String)
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[<>:""\/\|?*]"
    NoSpec = .Replace(s, IIf(Len(r) > 0, r, ""))
End With
End Function

 

[S Oberlander]

Cool!
So I did fn = NoSpec(ActiveCell, "_") and my code no longer stops for an invalid filename.