Find and Replace Multiple Characters

[ellison]

Hi, I was wondering if somebody could help with replacing multiple characters (listed below) with "_"
The active sheet is "Sheet1"
And the data is in in columns A:Z
Some cells may have multiple instances of any of these characters.

' (apostrophe)
" (inverted comma)
~
*
#
?
$
!
[
]
<
>
^
\
=

Huge thanks for any help you can provide!

 

[Peter_SSs]

Try this with a copy of your data.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Replace_Characters()
  Dim itm As Variant
  
  Const myCharacters As String = "33 34 35 36 39 42 60 61 62 63 91 92 93 94 126"
  
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Columns("A:Z"))
    For Each itm In Split(myCharacters)
      .Replace What:="~" & Chr(itm), Replacement:="_", LookAt:=xlPart
    Next itm
  End With
  Application.ScreenUpdating = True
End Sub

 

[ellison]

Hi Peter, that works beautifullyl!

And thanks for the reminder, I updated my profile to to show that I'm usoing Office 365 on a Windows platform.

I'm off to tidy up about a hundred files like they're never been tidied up before ! ?

Joking aside, HUGE thanks again.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your profile.

 

[OilEconomist]

Try this with a copy of your data.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Replace_Characters()
  Dim itm As Variant
 
  Const myCharacters As String = "33 34 35 36 39 42 60 61 62 63 91 92 93 94 126"
 
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Columns("A:Z"))
    For Each itm In Split(myCharacters)
      .Replace What:="~" & Chr(itm), Replacement:="_", LookAt:=xlPart
    Next itm
  End With
  Application.ScreenUpdating = True
End Sub

Hi @Peter_SSs and thanks for continuously helping users like me. If have searched all over the internet and message boards, and finally when I was about to post here, my title brought this. I tried your code and it remove all my entries. Basically what I'm trying to do is remove certain characters if they are part of the entry. For example, if I have "*Hello," I would like to remove the "*" so all I have left is "Hello".

I would like write two sets of code, one where it just replaces in column C, and the next code is anywhere in the sheet. It would basically like to remove the characters:
.
~
*
**
-
#

So in your code what is the purpose of the code

Const myCharacters As String = "33 34 35 36 39 42 60 61 62 63 91 92 93 94 126"

I was using the following, but I need to repeat it for every character I would like to remove:

Sheets("Sheet1").Columns("C").Replace "+ ", "", xlWhole, , , , False, False

Thanks in advance.

 

[Peter_SSs]

One example is not much to go on so could we have a variety of sample data and expected results with XL2BB?

So in your code what is the purpose of the code

Const myCharacters As String = "33 34 35 36 39 42 60 61 62 63 91 92 93 94 126"

They are the ASCII codes for the 15 characters the OP wanted to be removed as per the list in post 1.

 

[ellison]

One example is not much to go on so could we have a variety of sample data and expected results with XL2BB?


They are the ASCII codes for the 15 characters the OP wanted to be removed as per the list in post 1.

Just to confirm, Peter's code works beautifully & does exactly what we need.
Hope this helps!