Ensure that the special character appears in the Find what text box in the Find and Replace dialog box. (The copyright symbol appears in this example.) Figure 1. Select the special character in your text.

To insert special characters directly into the Find and Replace dialog box:ġ. Twelve special characters can be inserted directly into the Find and Replace dialog box by selecting the character: This last UDF can be sped up a bit by using early-binding, and, if you have many strings to process, by doing it within VBA arrays.Enter Special Characters Directly into the Find and Replace Dialog Box‎‏ I don't know if it would be any faster, but here is another UDF using Regular Expressions which can also do the same thing: Option Explicit

If this is not an isolated instance, but you need to have a routine to remove any characters which cannot be displayed by the VBA editor, you can try this: Option Explicitĭim i As Long, l As Long, sTemp As StringĪnd, to show it works, here are screenshots of a MsgBox generated from the raw text, and the text produced by the removeChars UDF above: If you are not familiar, suggest internet search for ZWJ, the VBA ChrW function, and the Excel UNICHAR function. Or, the equivalent VBA code: replace(,chrw(8205),'') If you need to remove them, you can do it on the worksheet with =SUBSTITUTE(A1,UNICHAR(8205),'') So, although this works perfectly OK on your worksheet, it converts to ?'s in the VBA editor. Your text string includes the Unicode ZWJ (zero-width-joiner) character for which the decimal code is 8205. Select B2:D2 and fill down as far as needed. In Excel, you might do: A1: your_text_string You can determine the errant characters by displaying each character one at a time, and converting to both standard character code, and Unicode character codes. I'm not sure why you feel you need to remove these characters, but: It can manipulate higher characters, but they won't be displayed in the immediate window (or message boxes, for that matter). Specifically, it's ability to display characters is limited to those on the Windows Code 2 (the 8-byte characters). The problem is that the VBA Editor does not display most UNICODE characters.

Finding hidden characters in excel 2013