Replace Numbers followed by Non-Printable Characters Chr(25) into Numbers followed by double quotati-CodePudding

I need to replace non-printable characters with double quotation "
The problem is, this bad character Chr(25) can come after number, single time or twice and even comes after number and double quotation "
If I used excel clean function ,that will remove all Chr(25) and not replace it.

Range("C2") = WorksheetFunction.Clean(Range("B2"))

I also tried to use vba Replace function, but again the problem is count and position of Non-Printable Characters:

Range("C2") = Replace(Range("B2"), Chr(25) & Chr(25), """")

'If Chr(25) is single, this code will replace and add again

In advance, grateful for all your help.

CodePudding user response：

I'd suggest a regular expression to catch and replace these characters:

Function RegexReplace(s As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = "(\d )[""']*\u0019 "
    .Global = True
    RegexReplace = .Replace(s, "$1""")
End With
    
End Function

See an online

Formula in B1:

=RegexReplace(A1)

Note: If you don't need to specify the digits, you could leave (\d ) out and just use ["']*\u0019 with a simple replacement of a single ".