none
Text to Column Code RRS feed

  • 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!

    Thursday, March 30, 2017 2:05 PM

Answers

  • 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)

    • Marked as answer by D.Hanna Thursday, March 30, 2017 8:15 PM
    Thursday, March 30, 2017 8:00 PM

All replies

  • 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)

    Thursday, March 30, 2017 4:21 PM
  • Thank you for your reply.

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


    • Edited by D.Hanna Thursday, March 30, 2017 5:18 PM
    Thursday, March 30, 2017 5:17 PM
  • 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)

    • Marked as answer by D.Hanna Thursday, March 30, 2017 8:15 PM
    Thursday, March 30, 2017 8:00 PM
  • Thank you so much!
    Thursday, March 30, 2017 8:15 PM