Remove Special Characters :

[Rajan_Verma]

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 :



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]

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...


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...


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...


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.

[Excel Fox]

Bingo on the last range, and a more efficient code

[Rajan_Verma]

Great :)