revamp remove special characters routine

Posted on 2011-04-25
Last Modified: 2012-05-11
vba 2003

excel routine...

The routine below gets rid of special characters.

What I need:

I also want it to remove some special words:  i.e.  
IN OR In  or iN



Public Function SquishChars(sVal As String) As String

    Dim i As Integer
    Dim j As Integer
    Dim sRET As String
    Dim c As String
    For i = 1 To Len(sVal)
        c = Mid(sVal, i, 1)
        j = Asc(c)
        If j >= 48 And j <= 57 Then 'include numbers 0-9
            sRET = sRET & c
        ElseIf j >= 65 And j <= 90 Then 'include uppercase A-Z
            sRET = sRET & c
        ElseIf j >= 97 And j <= 122 Then 'include lowercase a-z
            sRET = sRET & c
        End If
    SquishChars = sRET

End Function

Open in new window

Question by:fordraiders
    LVL 22

    Expert Comment

    There are several viable solutions for this, some being more elaborate and other which can be set to work very efficiently within a set of rules.

    For example, if the words you want to remove are just a string of characters, you can add something like this:

        SquishChars = Replace(sRET,"In","")

    However, if you don't want it to remove "Invert" then you'll need a new strategy.

    Are the words you want to remove always delimited by something (like surrounded with spaces, or preceded by space and followed by new line)?

    How many words do you want to remove?
    Are they case sensitive?
    Are there some cases where you want to keep the text (as "Invert" above should not remove "In")?
    LVL 3

    Author Comment


    How many words do you want to remove?  = 10-15 words

    case sensitive  =  yes

    invert = no   should only be exact term..

    LVL 22

    Expert Comment

    so how do you determine the start and end of a word? are they sentence-style (separated by spaces and standard punctuation: .?!,:;space)? Are other symbols going to be there?

    Let me try to whip something up for you to extend your existing routine.
    LVL 22

    Accepted Solution

    OK...I haven't totally tested this, but give it a shot to see how it goes.
    I think you'll see how you can add extra words here:

    Const cExcludeItems As String = "in;inch;inches;pack"
    Private Function SquishWords(FullText As String) As String
        Dim strResult As String
        Dim strExcludeArray() As String
        Dim iWordCount As Integer
        Dim iWordCntr As Integer
        Dim iCharCntr As Integer
        Dim iWordPosition As Integer
        Dim strChar As String
        Dim strWord As String
        Const cExcludeItems As String = "in;inch;inches;pack"
        Const cDelimiterList As String = " .!?,;:" & vbCr
        strResult = FullText
        strExcludeArray = Split(cExcludeItems, ";")
        iWordCount = UBound(strExcludeArray)
        For iWordCntr = 0 To iWordCount
            strWord = strExcludeArray(iWordCntr)
            'Debug.Print strWord
            For iCharCntr = 1 To Len(cDelimiterList)
                strChar = Mid(cDelimiterList, iCharCntr, 1)
                'Debug.Print strChar & " " & strResult
                strResult = Replace(strResult, " " & strWord & strChar, "", , , vbTextCompare)
        SquishWords = strResult
    End Function

    Open in new window

    LVL 3

    Author Comment

    separated by spaces  = yes
    LVL 3

    Author Comment

    rsp, The function is working fine.. cD variable is not getting changed back into the module routine.. ?

    Public Function GetDataDesc(rng As Range) As String
    ' DO SOME WORK ....

    If CStr(rng.Value) <> "" Then
       'split on array

    ' i.e. Example string  "Terminal,Ring,12-10 in AWG,#6 Stud,PK50"

         cD = " " & CStr(rng.Value) & " "
          cD = Replace(cD, ",", " ")
           cD = cD  ' NO COMMAS SO FAR   i.e. "Terminal Ring 12-10 in AWG #6 Stud PK50

       SquishWords cD

    '   cD should be  "Terminal Ring 12-10 AWG #6 Stud"

       cD = cD  this does not reflect changes the function had accomplished
    '  still showing as "Terminal Ring 12-10 in AWG #6 Stud PK50"
         cD = Replace(cD, "  ", " ")


    End Function
    LVL 3

    Author Comment

    rsp, My fault I just realized I was giving the wrong function...bUt your solution resolved both my issues...

    I was not trying to mesh all my characters together..

    Your firstg suggestion of using Replace() me get rid of commas...I then used your routine to get rid of unwanted words and keep a <space> between those words  !!!!

    Very nicely done.. !

    So Thanks for the help...
    I just set cD as a public variable and it is fine now ...!
    Thanks again !

    LVL 3

    Author Closing Comment

    Thanks Great solution !
    LVL 22

    Expert Comment

    Glad it worked so well since I was out yesterday and couldn't follow up until today :)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now