revamp remove special characters routine

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

Who is Participating?
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

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")?
FordraidersAuthor Commented:

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

case sensitive  =  yes

invert = no   should only be exact term..

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
FordraidersAuthor Commented:
separated by spaces  = yes
FordraidersAuthor Commented:
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

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
FordraidersAuthor Commented:
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 !

FordraidersAuthor Commented:
Thanks Great solution !
Glad it worked so well since I was out yesterday and couldn't follow up until today :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.