VBA Script - Find and Replace Multiple Characters

[makingtrails]

Hi Guys,

I have been looking for a solution to a "Find and Replace" script but having problems finding the exact one for me requirements.

Basically I have 10 columns (A2, B2, C2 etc) all with text that I export to an external database via CSV but within this text are invalid characters which my database doesn't like.

These are for instance

— ™ „ “ ” …

What I need is a VBA script which I can run which replaces these characters with one's I can set in the script.

For instance

Find "—" & Replace with "-"
Find "™" & Replace with ""
Find "…" & Replace with "..."

Can anyone point me to post that explains how to do this?

Thanks

J

 

[texasalynn]

take a look at this
http://www.mrexcel.com/forum/excel-questions/341057

 

[texasalynn]

here is vba code if you want:

 Sub Replace_Characters()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("S1:S" & LR).Replace What:=Chr(153), Replacement:="", LookAt:=xlPart
Range("S1:S" & LR).Replace What:=Chr(151), Replacement:="--", LookAt:=xlPart
Range("S1:S" & LR).Replace What:=Chr(133), Replacement:="...", LookAt:=xlPart
End Sub

 

[JLGWhiz]

The code is pretty simple to do replacements.

Sub op()
Sheets(1).Range("A1:F20").Replace "_", ""
End Sub

If you have several to do, then you might want to use a For next loop by listing your target items in column A for example and the replacement in column B, beginning on row 2 for purpose of illustration. Let's assume six characters that you want to replace. So the characters will be in Range("A2:B7") and the Data to be searched will be in Range("A10:Z100") for example.

Sub replc()
Dim sh As Worksheet, rng As Range
Set sh = Sheets(1) 'Replace with actual sheet name
Set rng = sh.Range("A10:Z100")
 For i = 2 To 7
  With sh
   On Error Rexume Next
   rng.Replace(.Cells(i, 1).Value, .Cells(i, 2).Value)
   On Error GoTo 0
  End With
 Next
End Sub

The second code would walk down the list of characters in column A, replacing them with the character or nullstring in column B.

 

[BrandynBlaze]

I am trying to do the same thing except that there are A LOT of characters that I need to find and replace for. I'd rather not write a case for every single one and was wondering if there is an easy way to reference another sheet with all the values using vlookup or something? It seems like it would be a pretty hefty program if it ran vLookup to compare every single character for every single case.

 

[makingtrails]

here is vba code if you want:

 Sub Replace_Characters()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("S1:S" & LR).Replace What:=Chr(153), Replacement:="", LookAt:=xlPart
Range("S1:S" & LR).Replace What:=Chr(151), Replacement:="--", LookAt:=xlPart
Range("S1:S" & LR).Replace What:=Chr(133), Replacement:="...", LookAt:=xlPart
End Sub

This works great for me however because I have data that is always changing I need it to "Find and Replace" the whole of the columns for multiple columns.

For example:

Only "Find and Replace" for all cells in Columns E - N

Thank you, your help is much appreciated

 

[texasalynn]

change this:

Range("S1:S" & LR)
to your area
Range("E:N")

 

[JLGWhiz]

You were correct, Resume was misspelled. Did you try to correct it to see if the code would run without error?

Sub replc()
Dim sh As Worksheet, rng As Range
Set sh = Sheets(1) 'Replace with actual sheet name
Set rng = sh.Range("A10:Z100")
 For i = 2 To 7
  With sh
   On Error Resume Next
   rng.Replace(.Cells(i, 1).Value, .Cells(i, 2).Value)
   On Error GoTo 0
  End With
 Next
End Sub

 

[yard2011]

Hi JLG,

I am trying use this VBA in Excel 97, but with Syntax error result in

rng.Replace(.Cells(i, 2).Value, .Cells(i, 1).Value)
rng.Replace(.Cells(i, 4).Value, .Cells(i, 3).Value) .

is this Replace syntax valid only in Excel 2013 ?

thank you