Microsoft Office Forums Find and replace multiple characters macro

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2019, 12:09 AM
Urraco Urraco is offline Find and replace multiple characters macro Windows 8 Find and replace multiple characters macro Office 2016
Novice
Find and replace multiple characters macro
 
Join Date: Apr 2018
Posts: 18
Urraco is on a distinguished road
Default 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
Attached Images
File Type: jpg 3_09-06-2019.jpg (71.3 KB, 19 views)
Reply With Quote
  #2  
Old 09-06-2019, 08:23 AM
Logit Logit is offline Find and replace multiple characters macro Windows 10 Find and replace multiple characters macro Office 2007
Expert
 
Join Date: Jan 2017
Posts: 272
Logit is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 09-08-2019, 11:35 PM
Urraco Urraco is offline Find and replace multiple characters macro Windows 8 Find and replace multiple characters macro Office 2016
Novice
Find and replace multiple characters macro
 
Join Date: Apr 2018
Posts: 18
Urraco is on a distinguished road
Default

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

Quote:
Originally Posted by Logit View Post
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
Reply With Quote
  #4  
Old 09-09-2019, 07:52 AM
Logit Logit is offline Find and replace multiple characters macro Windows 10 Find and replace multiple characters macro Office 2007
Expert
 
Join Date: Jan 2017
Posts: 272
Logit is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 09-10-2019, 01:13 AM
Urraco Urraco is offline Find and replace multiple characters macro Windows 8 Find and replace multiple characters macro Office 2016
Novice
Find and replace multiple characters macro
 
Join Date: Apr 2018
Posts: 18
Urraco is on a distinguished road
Default

it's good
Thanks!


Quote:
Originally Posted by Logit View Post
See if this edit does it :
Reply With Quote
  #6  
Old 09-10-2019, 07:25 AM
Logit Logit is offline Find and replace multiple characters macro Windows 10 Find and replace multiple characters macro Office 2007
Expert
 
Join Date: Jan 2017
Posts: 272
Logit is on a distinguished road
Default

You are welcome.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and replace multiple characters macro VBA Find&Replace all bold, itlaic, underlined and highlighted words/characters Kalü Word VBA 22 04-24-2018 05:35 AM
Find and replace multiple characters macro Macro to Find & Replace Font formats for Multiple Values GemBox Word 6 03-12-2018 05:24 AM
Find and replace multiple characters macro Combining Characters in Find & Replace Surge Word 5 01-11-2017 03:43 PM
Find and replace multiple characters macro 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


All times are GMT -7. The time now is 04:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft