Results 1 to 4 of 4

Thread: Remove Special Characters :

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

    Lightbulb Remove Special Characters :

    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

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    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.
    Last edited by Rick Rothstein; 02-20-2012 at 11:39 PM.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Bingo on the last range, and a more efficient code
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Great

Similar Threads

  1. Find Mismatch Characters:
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 06-06-2013, 07:53 PM
  2. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  3. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  4. Replies: 2
    Last Post: 02-23-2013, 09:18 PM
  5. Replies: 2
    Last Post: 01-22-2013, 11:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •