replace some ascii characters with blanks

[mscallisto]

I have opened a text file with excel and found some embedded ascii characters other than a-z, A-Z, 0-9 and +-)(*&^%$#@! etc. in columns B and C (text format)

I know I can replace all E's with a question mark ? with the following or simply do a replace within the spreadsheet.

    Cells.Replace What:="E", Replacement:="?", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

But how can I replace anything other than a-z, A-Z, 0-9 and +-)(*&^%$#@! with a space ?

 

[TonyJollans]

You could look at the [blue][tt]CLEAN[/tt][/blue] function which will strip out ascii values less than 32 but. AFAIK, there's no easy way beyond that to change or remove arbitrary characters - you have to work on a case by case basis.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[hisbadbanana]

have a look at Ken's post in this thread.

excel special characters

 

[mscallisto]

Thanks Tony but I can't seem to get clean() to work unless I clean to another column. Clean() to the same column yields the circular error.

Thanks hisbadbanana but I tried (with no luck) a variation of Kens:

Sub Replace_Tabs_with_Null()
' replace tabs ascii 009 with null in cells a1-a99 and b1-b99
    Dim x As Long
    Dim cell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cell In range("A1:A99,B1:B99")
        x = cell.Row
        Selection.Replace what:=chr(9), Replacement:="", lookat:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=True
    Next
     Application.ScreenUpdating = True
End Sub