#1
|
|||
|
|||
Find and replace multiple characters macro
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thank you
it works but it can I search only in a specific column? "A" column for example Quote:
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
|
#6
|
|||
|
|||
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 |