MS Excel find and replace macro
Posted
by
william
on Super User
See other posts from Super User
or by william
Published on 2012-10-17T16:35:48Z
Indexed on
2012/10/17
17:05 UTC
Read the original article
Hit count: 232
excel-2007
|vba
I have written a macro to remove special characters in a sheet based on ascii values but the problem with it is that it is replacing the cell content. For example p;j;h
which should become p,j,h
is becoming ,,
(missing the data). Do I need to include any additional statements, or how else to adjust my code?
sub specialcharecters()
Dim i As Long
For i = 32 To 43
Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
Selection.Replace what:="~*", replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
For i = 45 To 47
Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
For i = 58 To 64
Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
For i = 123 To 125
Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
Selection.Replace what:="~~", replacement:=", ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
END sub
© Super User or respective owner