How to remove the non-letter, non-numeric charaters? RRS feed

  • General discussion

  • I want to import exterior data to a new excel sheet and name this sheet after the file name. However, since the file name contains non-alphanumeric characters like "-" or "_", I'll have to remove them before I can name the new sheet.

    Does anyone know how to do this?

    Thank you !

    Sunday, October 6, 2013 9:38 PM

All replies

  • Function FilterSludge(ByRef strName As String)
     'Finds unwanted characters and replaces them
     'Jim Cone - Portland, Oregon
     ': / \ ? * [ ] are not allowed in a worksheet name.
      Dim BadChars As String
      Dim N As Long
     'Add or remove characters from/to BadChars as desired.
      BadChars = "<>=@*[]\!&|():%{}," & Chr$(34)  'Chr(34) is "
      For N = 1 To Len(BadChars)
        strName = Application.WorksheetFunction _
                       .Substitute(strName, Mid$(BadChars, N, 1), vbNullString)
      Next 'N
      FilterSludge = strName
    End Function

    Sub CallTheFilterFunction()
       MsgBox FilterSludge("nowis<>=@*[]\!&|the""time<>=@*")
    End Sub
    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 1:28 AM
    Sunday, October 6, 2013 10:59 PM