Replace symbols from text string

[mikeymay]

I have a column of text strings which some contain the ˜ ™ € Â and/or â symbols.

I though using the Chr function within VBA would work but it doesn;t seem to.

Can anyone help with what I need to do replace these characters/symbols from the text strings?


TIA

 

[JoeMo]

Can you post the code that doesn't work and some sample strings?

 

[mikeymay]

A sample of the text is
Personalised "Thank you for" Board Description ~Customise upto 5 reasons you want to thank them Change the name to any you’d like up to 20 characters. Size 20cm x 30cm. Material Hevea wood.

I was going to do a bulk change of the column for the ~ symbol but this basically clears the whole column

Range("C:C").Replace What:=Chr(126), Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

I also tried the following on a single cell but the INSTR function returned 0

strDescription = rngRow.Offset(0, 2)
      
      intDescription = InStr(1, strDescription, Chr(128))

I haven't tried the above for the other 4 symbols I need to remove but I'm assuming some if not all won't work.

 

[JoeMo]

This works for me.

Sub RemoveCertainSymbols()
'select the range of cells to clean up first, then run this macro
Dim CertainSymbols As Variant, R As Range
Set R = Selection
CertainSymbols = Array(Chr(128), Chr(152), Chr(153), Chr(194), Chr(226))
For i = LBound(CertainSymbols) To UBound(CertainSymbols)
    R.Replace what:=CertainSymbols(i), replacement:="", lookat:=xlPart
Next i
End Sub

 

[mikeymay]

I plugged in your code as follows

Set R = Range("C:C")

CertainSymbols = Array(Chr(128), Chr(152), Chr(153), Chr(194), Chr(226))

For intChar = LBound(CertainSymbols) To UBound(CertainSymbols)
    R.Replace what:=CertainSymbols(intChar), replacement:="", lookat:=xlPart
Next intChar

On the 2nd intChar it clears the whole column of all contents.

 

[JoeMo]

Don't know why that would happen. As I said, the specific code I posted works for me using your sample data from post #3 .

 

[TonyUK72]

FORMULA: Assuming your text starts in A1, Place formula in B1, copy down as needed.

=SUBSTITUTE(SUBSTITUTE(A1,"~",""),"’","'")

Once you have the desired results in column B simply select them all and copy / paste special VALUES back into column A. Finish by deleting column B.

My formula also factors in the ' between the you & d to give you'd