Answered by:
Text to Column Code

Question
-
Hi,
I have the following function to replace special characters with spaceFunction 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 FunctionHow 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 SubRegards, 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 SubRegards, 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 SubRegards, 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