asked on

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

Inches
Inch
pack
Pack

Thanks\
fordraiders

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
    Next
    SquishChars = sRET

End Function

Open in new window

rspahitz

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")?

Fordraiders

ASKER

rsp,

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

case sensitive = yes

invert = no should only be exact term..

rspahitz

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.

ASKER CERTIFIED SOLUTION

rspahitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

separated by spaces = yes

Fordraiders

ASKER

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, " ", " ")

' DO MORE STUFF

End Function

Fordraiders

ASKER

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()...help 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 !

Fordraiders

ASKER

Thanks Great solution !

rspahitz

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