Replacing multiple invalid characters in a sheet name

calendar_today Asked Dec 21, 2011
thumb_up 7 upvotes
history Updated April 16, 2026

Question posted 2011 · +3 upvotes

I am writing a fairly large macro

  • which ends by saving the workbook with a name that the user inputted earlier on.
  • I am using the title of the sheet for this, so I need to strip out any reserved characters that will throw up file system errors (/*|?:<>").

I would like to avoid having to use a large number of Replace() statements, so is there any way to replace multiple characters with an underscore in one statement – something more like a ?

Thanks all!

Accepted answer +7 upvotes

Happily you will find that most of these characters are invalid already as sheet name characters, ie *?[] etc

But as you have said already, use a 🙂

Something like below to return True for valid strings, False for invalid ones.

I have added your items above, escaping special characters as needed.

[Updated: added replacement Function] 

Sub TestCleanNames()
    Dim StrArr()
    Dim StrTst
    StrArr = Array("Am i ok", "Sheet||a", "audited??", "Sheet1")
    For Each StrTst In StrArr
        MsgBox StrTst & " = " & ValidSheetName(CStr(StrTst)) & vbNewLine & CleanSheetName(CStr(StrTst))
    Next
End Sub

valid name function

Function ValidSheetName(strIn As String) As Boolean
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = "[<>*\/?|]"
    ValidSheetName = Not objRegex.test(strIn)
End Function

clean function

Function CleanSheetName(strIn As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "[<>*\/?|]"
        CleanSheetName = .Replace(strIn, "_")
    End With
End Function

3 code variants in this answer

  • Variant 1 — 8 lines, starts with Sub TestCleanNames()
  • Variant 2 — 6 lines, starts with Function ValidSheetName(strIn As String) As Boolean
  • Variant 3 — 9 lines, starts with Function CleanSheetName(strIn As String) As String

Top excel-vba Q&A (6)

+7 upvotes ranks this answer #101 out of 136 excel-vba solutions on this site .
excel-vba