a macro to replace special characters

  • Thread starter Thread starter deepan
  • Start date Start date
D

deepan

HI,

I am looking a macro function to eliminate the special
characters like "[", square boxes....is there a way do
it...actually i having text field...which contains such
characters...actually they were imported from excel...

Pls give me procedure in detail...becos i am a beginner.

Thanks for your help.
 
Good morning

The following function should would for you.
Simply copy / paste the following into a new module. Just make sure that the
charater(s) you wish to strip are included the the Case statement.

Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.


Function StripString(MyStr As Variant) As Variant
' Print StripString("Test s t ring.#,-,&,/,*")
'The invalid characters (".#,-") are successfully stripped from the test
'string.


On Error GoTo StripStringError

Dim strChar As String, strHoldString As String
Dim i As Integer
' Exit if the passed value is null.
If IsNull(MyStr) Then Exit Function
' Exit if the passed value is not a string.
If VarType(MyStr) <> 8 Then Exit Function
' Check each value for invalid characters.
For i = 1 To Len(MyStr)
strChar = Mid$(MyStr, i, 1)
Select Case strChar
Case ".", "!", "%", "#", ",", "-", "&", "/", "*", " ", "@", "$",
"^", "(", ")", ">", "<"
' Do nothing
'strHoldString = strHoldString & Chr(32)
Case Else
strHoldString = strHoldString & strChar
End Select
Next i

' Pass back corrected string.
StripString = strHoldString

StripStringEnd:
Exit Function

StripStringError:
MsgBox Error$
Resume StripStringEnd
End Function
 
Back
Top