Excel VBA Macro: Remove Special Characters from a String (or Filename)

Excel VBA Macro: Remove Special Characters from a String (or Filename). In this video, we go over the nine special characters that generate the following message when trying to save a file: "A filename cannot contain any of the following characters...". We then write code that uses an array of special characters and a For Loop that replaces any special characters in a string (that we type directly in the code and then again based on cell value) with the character of our choice, or no character at all.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub remove_spec_chars()

Dim str As String
Dim spec_chars As Variant
Dim x As Variant

str = ThisWorkbook.Sheets(1).Cells(1, 1).Text
spec_chars = Array("LT", "GT", "\", "/", "?", "*", ":", """", "|")

For Each x In spec_chars

str = Replace(str, x, " ")

Next x

MsgBox str

End Sub

#ExcelVBA #ExcelMacro

[duanedonaldson]

Thank you very for the algorithm, will you be finishing this to make the new file/folder name useful and not only in a Msgbox? but as a SaveAs useful name or change the name in cell 1, 1 and create the new name in a cell off to the right-hand side someplace. I also recommend scripting your dialog and creating notes in advance as to the content of your video, it was a bit awkward trying to figure out the name of your file in cell 1, 1. Looking forward to more cool videos.