Find and replace multiple characters macro

Urraco · #1 09-06-2019, 12:09 AM

Hello everybody

I have a text column A
I would like to change multiple characters at once with a single character in same column
like in image below
Thanks

Nick

Logit · #2 09-06-2019, 08:23 AM

Code:

Option Explicit

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As String
Dim x As Long

fndList = Array("$", "%", "&")  '<-- list individual characters here
rplcList = "_"

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList, _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x

End Sub

Urraco · #3 09-08-2019, 11:35 PM

Thank you
it works but it can I search only in a specific column?
"A" column for example

Quote:

Originally Posted by Logit

Code:

Option Explicit

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As String
Dim x As Long

fndList = Array("$", "%", "&")  '<-- list individual characters here
rplcList = "_"

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList, _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x

End Sub

Logit · #4 09-09-2019, 07:52 AM

See if this edit does it :

Code:

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As String
Dim x As Long

fndList = Array("$", "%", "&")  '<-- list individual characters here
rplcList = "_"

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Range("A2:A25").Replace What:=fndList(x), Replacement:=rplcList, _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x

End Sub

Urraco · #5 09-10-2019, 01:13 AM

it's good
Thanks!

Quote:

Originally Posted by Logit

See if this edit does it :

Logit · #6 09-10-2019, 07:25 AM

You are welcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Combining Characters in Find & Replace	Surge	Word	6	03-10-2020 12:42 AM
VBA Find&Replace all bold, itlaic, underlined and highlighted words/characters	Kalü	Word VBA	22	04-24-2018 05:35 AM
Macro to Find & Replace Font formats for Multiple Values	GemBox	Word	6	03-12-2018 05:24 AM
Find and Replace some characters with Bullets	kjxavier	Word	1	01-02-2015 12:15 AM
Macro for find/replace (including headers and footers) for multiple documents	jpb103	Word VBA	2	05-16-2014 04:59 AM