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

user_17797849 0
Viewed 46

enter image description here

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.

vba excel
1 Answers

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 demo of the pattern which means:

  • (\d+) - Capture any 1+ digits in a capture group;
  • ["']* - Match 0+ double/single quotes;
  • \u0019+ - Match 1+ 'END OF MEDIUM' characters.

Replace with $1" means to input the captured digits followed by a double quote.

enter image description here

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 ".

user_97581940
Related