Replace non-printable characters with " (Inch sign) VBA Excel-CodePudding

I need to replace non-printable characters with " (Inch sign).
I tried to use excel clean function and other UDF functions, but it just remove and not replace.
Note: non-printable characters are highlighted in blue on the above photo and it's position is random on the cells.
this is a sample string file Link`
The expected correct output should be 12"x14" LPG . OUTLET OCT-SEP@ process
In advance grateful for useful comments and answer.

CodePudding user response：

As per my comment, you can try:

=SUBSTITUTE(A1,CHAR(25)&CHAR(25),CHAR(34))

Or the VBA pseudo-code:

[A1] = [A1].Replace(Chr(25) & Chr(25), Chr(34))

Where [A1] is the obvious placeholder for the range-object you would want to use with proper and absolute referencing.

With ms365 newest functions, we could also use:

=TEXTJOIN(CHAR(34),,TEXTSPLIT(A1,CHAR(25)))

CodePudding user response：

You can try this :

Cells.Replace What:="[The caracter to replace]", Replacement:=""""

CodePudding user response：

You can use Regular Expressions within a UDF to create a flexible method to replace "bad" characters, when you don't know exactly what they are.

In the UDF below, I show two pattern options, but others are possible.

One is to replace all characters with a character code >127
the second is to replace all characters with a charcter code >255

Option Explicit
Function ReplaceBadChars(str As String, replWith As String) As String
    Dim RE As Object
    
Set RE = CreateObject("Vbscript.Regexp")
With RE
    .Pattern = "[\u0080-\uFFFF]" 'to replace all characters with code >127 or
    '.Pattern = "[\u0100-\uFFFF]" 'to replace all characters with code >255
    .Global = True
    ReplaceBadChars = .Replace(str, replWith)
End With
End Function

On the worksheet you can use, for example:

=ReplaceBadChars(A1,"""")

Or you could use it in a macro if you wanted to process a column of data without adding an extra column.

Note: I am uncertain as to whether there might be an efficiency difference using a smaller negated character class (eg: [^\x00-\x79] instead of the character class I showed in the code. But if, as written, execution seems slow, I'd try this change)