Remove Special Characters :

**Rajan_Verma** · 10-20-2011, 09:15 PM

If You want to Remove Extra Character from you String , You want to Keep only Alphabets and Numeric Character You can use the Below Function :

Code:

Public Function SheetName(Shname As String) As String Dim Cod As Integer Dim ShN As String
            For i = 1 To Len(Shname)
                Cod = Asc(Mid(Shname, i, 1))
                        If (Cod > 64 And Cod < 91) Or (Cod > 96 And Cod < 123) Or (Cod > 79 And Cod < 90) Then
                        ShN = ShN & Mid(Shname, i, 1)
                        End If
            Next
            SheetName = ShN
End Function

**Rick Rothstein** · 02-20-2012, 02:47 AM

Originally Posted by Rajan_Verma

Code:

If (Cod > 64 And Cod < 91) Or (Cod > 96 And Cod < 123) Or (Cod > 79 And Cod < 90) Then

Are your ranges correct for the above quoted statement? The first range (65:90) completely encompasses the last one (80:89). Since that last range covers 10 characters, I presume you meant to cover (48:57) which are the digits you mentioned when you said "Alphabets and Numeric Character". So I am guessing you meant that statement to be this instead...

Code:

If (Cod > 64 And Cod < 91) Or (Cod > 96 And Cod < 123) Or (Cod > 47 And Cod < 58) Then

A simpler way to write this line of code is to use the Like operator. Here is how your code would look using it...

Code:

Public Function SheetName(Shname As String) As String
  Dim i As Long
  Dim Cod As Long
  Dim ShN As String
  For i = 1 To Len(Shname)
    If Mid(Shname, i, 1) Like "[A-Za-z0-9]" Then
      ShN = ShN & Mid(Shname, i, 1)
    End If
  Next
  SheetName = ShN
End Function

Note that I added a declaration for the i variable (I use Option Explicit so that I am forced to declare all my variable) and I used Long data types instead of Integer as there is no real benefit to Integer on modern computers. I also note that you are removing spaces characters along with the other "Special Characters". Given that spaces in sheet names are somewhat common, did you really mean to parse them out of the SheetName function's returned value?

My personal preference is to not use repeated concatenations where possible, especially if I can couple it with a fast string function like Mid (when used both as a statement and a function). Here is the way I would write your SheetName function. I also like to avoid creating variable if there is a way to avoid it. Here is how I would write the SheetName function...

Code:

Public Function SheetName(ByVal Shname As String) As String
  Dim X As Long
  For X = 1 To Len(Shname)
    If Mid(Shname, X, 1) Like "[!A-Za-z0-9 ]" Then Mid(Shname, X) = Chr(1)
  Next
  SheetName = Replace(Shname, Chr(1), "")
End Function

The concept is to replace any non-AlphaNumeric, non-space characters (I chose to leave spaces in the returned value) with a non-printable character (I used the character whose ASCII code is 1) using the very fast "string stuffing" method afforded by Mid when used as a statement and then simply remove all of those non-printable characters using a single Replace function call (doing it this way avoids all concatenations). To do that, I made use of the Like operator's "not included" pattern character... the exclamation point (sometimes called the "bang" character). Also note that I changed the Shname argument to ByVal so that I could freely modify it within the code and not have those changes reflected back to the calling routine.