asked on

VBA - Convert all Special Characters to Text

Hello,

There seems to be many ways of doing this - but I'm after a smart function that will forfill all the following string manipulation.

Background : I have a table of Word Documents that are going to sucked out, using stream read - the content becomes a String, which will be indexed by SQL Server indexing.

I have successful got the 'string' but when inserted into the 'pure text' column, contains lots of special characts (appear as square) and such like. Don't really want a clumsy 'replace(str,vbxxx," ")' over and over.

I would like

1. Anything more that a double space to be a single
2. No tabs, lf, crlf or any form of formatting like that
3. Character 1-9, A-Z + "*",".","£" etc ... to be ALLOWED (as people will search on .Net, C++ etc)

So basically, any 'normal' ascii should be fine - but ALL those strange formatting characters to be replaced by single space.

A nice callable function is what I'm after ... myString = CleanMyString(myString)

Thanks y'awl !

S.S.

manthanein

Function CleanMyString(strwhat As String) As String

Dim icount As Integer
Dim iFirst As Integer
CleanMyString = ""
iFirst = 0
For icount = 1 To Len(strwhat)
If Asc(Mid(strwhat, icount, 1)) > 32 And Asc(Mid(strwhat, icount, 1)) < 127 Then
CleanMyString = CleanMyString & Mid(strwhat, icount, 1)
iFirst = 0
ElseIf Asc(Mid(strwhat, icount, 1)) = 32 Then
iFirst = iFirst + 1
If iFirst < 3 Then
CleanMyString = CleanMyString & Mid(strwhat, icount, 1)
End If
End If
Next
CleanMyString = Replace(CleanMyString, " ", " ")
End Function

SpencerSteel

ASKER

this looks great ... i say looks as my front-end *&*($ing database app has just got irreverseabley corrupt and guess who doesn't have a backup from the last day. (it opens - but no objects)

Gimme some time to get back to where I was and I'll close this question asap.

Thanks

S.S.

ASKER CERTIFIED SOLUTION

burtdav

membership

This solution is only available to members.

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

Start Free Trial

SpencerSteel

ASKER

Burtdav - don't understand that at all really (sorry!)

The other solution is working great, except one minor flaw - here's an example of the output

...Curriculum Vitae ProfileAn IT professional with over nine years functional...

In the original doc, the CV Profile would of been a heading, so you see we kind need an extra space of those CTRLs and I'm being REEEEEEALY lazy here - but i'm up to my eyeballs ... finish it off and the points are yours

Many thanks

S.S.

SpencerSteel

ASKER

Doh...all I needed was

CleanMyString = Replace(CleanMyString, Chr(13), " ")

at the end, just before

CleanMyString = Replace(CleanMyString, " ", " ")

Thanks

Great routine

Points awarded !

Take care

S.S.

SpencerSteel

ASKER

Just what I wanted - quickly !

Thanks

S.S.