Text to Column Code

Question

Hi,
I have the following function to replace special characters with space

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "=~\/-:*?""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

How can I apply it in a VBA to run on columns A,B,D,E,M

Any help is highly appreciated!

Answer 1

Sub ReplaceValues()
    Const sSpecialChars = "=~\/-:*?""<>|"
    Dim i As Long
    For i = 1 To Len(sSpecialChars)
        Range("A:A,B:B,D:E,M:M).Replace What:=Mid(sSpecialChars, i, 1), Replacement:=" ", LookAt:=xlPart
    Next i
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thank you for your reply.

The code is deleting the entire cell(s) content in the specified columns.

Answer 3

Oops, *, ? and ~ play a special role in find/replace. Try this version:

Sub ReplaceValues()
    Dim v As Variant
    For Each v In Array("=", "~~", "\", "/", "-", ":", "~*", "~?", Chr(34), "<", ">", "|")
        Range("A:A,B:B,D:E,M:M").Replace What:=v, Replacement:=" ", LookAt:=xlPart
    Next v
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

Thank you so much!