Results 1 to 6 of 6

Thread: Remove Special Characters From Text Or Remove Numbers From Text

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10

    Remove Special Characters From Text Or Remove Numbers From Text

    Code:
     
    Enum WhatToRemove
        Text
        Numeric
        SpecialCharacters
    End Enum
    Function RemoveCharacters(ByVal StringToReplace As String, WhatRemove As WhatToRemove) As String 
        Dim RegEx As Object
        
        Set RegEx = CreateObject("VBScript.RegExp")
        
        With RegEx
            .Global = True
            Select Case WhatRemove
                Case Text
                    .Pattern = "[A-Za-z]"
                Case Numeric
                    .Pattern = "\d"
                Case SpecialCharacters
                    .Pattern = "[\;+\.+\#+\!+\'+\*+\,+\+\^+\&+\*+\@+\(+\)]"
            End Select
        End With
        
        RemoveCharacters= RegEx.Replace(StringToReplace, "")
        
        Set RegEx = Nothing
        
    
    End Function
    Last edited by Excel Fox; 05-31-2013 at 12:27 PM. Reason: Revised After Rick's Comment

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Code:
     Function RemoveUnnecessaryCharacters(ByVal StringToReplace As String, WhatRemove As WhatToRemove) As String
    The part I highlighted in red is causing a "User-defined type not defined" error when I try to run your code?

    Also, do you think you could have given that function an any longer name?
    Last edited by Rick Rothstein; 05-30-2013 at 10:04 PM.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Rick, my bad. Updated the code with the missing Enum Type. And the longer name, yeah, a tad long I must admit
    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
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    @ExcelFox

    Thanks for sharing.
    I couldn't resist exploring; resulting in:

    Code:
    Enum gt_lost
        Text = 0
        Numeric = 1
        Specialcharacters = 2
    End Enum
    
    Function RmChr(ByVal Str_chain As String, y As gt_lost) As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = Array("[A-Za-z]", "\d", "[\~+\!+\@+\#+\$+\%+\^+\&+\*+\(+\)+\'+\-+\=+\{+\}+\[+\]+\:+\""+\++\;+\'+\<+\>+\?+\,+\.+\/]")(y)
            RmChr = .Replace(Str_chain, "")
        End With
    End Function

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    @snb and ExcelFox...

    And I couldn't help developting a non-RegExp function...
    Code:
    Enum WhatToRemove
      Text = 0
      numeric = 1
      SpecialCharacters = 2
    End Enum
    
    Function RemoveCharacters(ByVal StringToReplace As String, WhatRemove As WhatToRemove) As String
      Dim X As Long, Pattern() As String
      Pattern = Split("[A-Za-z] # [#~!@$%^&*()'={}[:""+;<>?,./\-]")
      If WhatRemove = SpecialCharacters Then StringToReplace = Replace(StringToReplace, "]", "")
      For X = 1 To Len(StringToReplace)
        If Mid(StringToReplace, X, 1) Like Pattern(WhatRemove) Then Mid(StringToReplace, X, 1) = Chr(1)
      Next
      RemoveCharacters = Replace(StringToReplace, Chr(1), "")
    End Function
    Note: I used snb's special characters list as it seemed more complete to me, although I also chose to remove the backslash character as well (you both allowed it to remain when special characters were to be removed).
    Last edited by Rick Rothstein; 05-31-2013 at 02:19 PM.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Since languages are more complex than the A-Z alphabet:

    Code:
    Enum gt_lost
        Text = 0
        Numeric = 1
        Specialcharacters = 2
        ASCII_Text = 3
        non_numeric = 4
    End Enum
    
    Function RmChr(ByVal Str_chain As String, y As gt_lost) As String
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = Array("[A-Za-z]", "\d", "[\" & Replace(StrConv("~!@#$%^&*()'-={}[]:""+;'<>?,./\|¦", 64), vbNullChar, "+\") & " ]", "\D+ ", "\D")(y)
            RmChr = .Replace(Str_chain, "")
        End With
    End Function
    
    Sub M_snb()
        MsgBox RmChr("aa~d  SDRT|éëïsdf 7\77ll""ll9 3 4,799 9@á¦#(*", 4)
    End Sub

Similar Threads

  1. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  2. Replies: 10
    Last Post: 12-10-2012, 11:28 PM
  3. Remove Numerics From Text
    By VIMAL CHALISSERY in forum Excel Help
    Replies: 2
    Last Post: 04-12-2012, 08:43 PM
  4. Remove Special Characters :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-06-2012, 09:41 PM
  5. Remove Zero / Zeroes From Alphanumeric Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-04-2011, 01:16 AM

Posting Permissions

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