How to remove special character from string?

Excel vba - remove *

• QUESTION: I was wondering if you could please assist me with a problem. I have data that I am trying to parse from previous records and I am trying to remove * (aka Asterix) from the cell's values. However,is treating it as a Wildcard and is removing all of the cell's values. This is how I have attempted to do it: Sub RemoveCharacters() Sheets("Parsed Information").Select Worksheets("Parsed Information").Range("A4:A100").Select Selection.Replace What:="*", Replacement:="", MatchCase:=True End Sub ANSWER: Robert when trying to relace a literal character that is also used as a wildcard such astrick or question mark, pair it with a Tilde Sub RemoveCharacters() Sheets("Parsed Information").Select Worksheets("Parsed Information").Range("A4:A100").Select Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub That worked for me. Not I specified xlPart. if you only want to replace it if it is the only character in the cell, then change xlpart to xlwhole. You should always specify this option since it is a persistent setting and could cause unexpected results if you don't know what it is. ---------- FOLLOW-UP ---------- QUESTION: Worked perfectly! One more slight problem, I was hoping your original answer would solve it but I cant seem to understand what is missing. Sub Parse() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Parsed Information") Dim dRow As Long Dim sRow As Long Dim SalesArray(1 To 1) As String SalesArray(1) = "*Sales #*" For sRow = 1 To Range("A5").End(xlUp).row If Cells(sRow, "A") Like SalesArray(X) Then Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "F") dRow = dRow + 1 End If Next sRow End Sub I have tried: SalesArray(1) = "*Sales ~#*" but it still doesn't work. I am basically trying to parse through numerous cells and pull out any cell that contains the"Sales #" in it, the macro will then copy it to another sheet in a different column. Any help would be a god send!

• Answer:

  Robert, If you are literally looking for cells that contain the substring "Sales #" then Sub Parse() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Parsed Information") Dim dRow As Long Dim sRow As Long Dim SalesArray(1 To 1) As String SalesArray(1) = "Sales #" For sRow = 1 To Range("A5").End(xlUp).row If Instr(1,Cells(sRow, "A"),SalesArray(1),vbTextcompare) > 0 Then Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "F") dRow = dRow + 1 End If Next sRow End Sub Not sure why you are using an array to hold "Sales #". If you are planning on including multiple search terms, then you will have to loop through that list. The use of Tilde is not technique used in the like operator. If you want to stick with the Like Operator then this is a demo from the immediate window: s = "the big dog likes the Sales # for this month" ? s like "*Sales [#]*" True so you enclose the # in square brackets because # is a special character in the Like operator. Sub Parse() Dim DestSheet As Worksheet Set DestSheet = Worksheets("Parsed Information") Dim dRow As Long Dim sRow As Long Dim SalesArray(1 To 1) As String SalesArray(1) = "*Sales [#]*" dRow = 6 For sRow = 1 To Range("A5").End(xlUp).row If Cells(sRow, "A") Like SalesArray(1) Then ' Next sRow End Sub