Remove or Replace special characters

[ijswalker]

Hi there,

I have an issue with a column in my query where I want to remove special characters. I have created the following function to use now and other times also.




Public Function con2(DESCR As String) As String

Special = ("[")

con2 = Replace(DESCR, Special, " ")


End Function


The only problem I have is that there are a few different special characters in the column and I only seem to be able to remove one special character.

Can anyone help?

Thanks

 

[CJ_London]

Are you trying to replace

[ or "[" or ("[")?

if it is just [ then your function would be

Public Function con2(DESCR As String) As String
    'replace [ with a space
    con2 = Replace(DESCR, "[", " ")
End Function

if you want to replace other characters at the same time then you can nest the replace function - e.g.

Public Function con2(DESCR As String) As String
    'replace [ and ] with a space
    con2 = Replace(Replace(DESCR, "[", " "),"]"," ")
End Function

 

[ijswalker]

CJ_London. That's perfect. Thank you so much.

 

[ijswalker]

I have just one more question relating to this. Is this function limited to two special characters or can I add more. I tried to add some more and I get an error.

 

[ijswalker]

Its ok. Figured it out. Thanks again.

 

[pbaldy]

Another option is to create a function that loops the characters in the string, and tests using Asc(). You can then select the characters you want to allow or reject. I've got one where I just allow letters, numbers and spaces. Anything else gets stripped out.