Declaring Characters in VBA Macro Code it’s never been so handy to remove all kinds of non-printable characters. Though the CLEAN function has its own limitations, the SUBSTITUTE function or Find & Replace works for pretty much all characters. The CLEAN, and SUBSTITUTE functions remove non-printable characters with ease. In this article, we have demonstrated 4 easy methods to remove non-printable characters in Excel. Read More: How to Remove the First Character from a String in Excel with VBA Step 6: Finally, drag the Fill Handle & It removes the rest of the cell’s non-printable characters. MText = mWF.Substitute(mText, MyText(J), mSubText) MText = mWF.Substitute(mText, Chr(I), mSubText) Step 3: Afterward, paste the code below in the Module window: Function RemoveNP(mText As String, Optional mSubText As String = ' ') Step 2: Then, from the toolbar, Click Insert & Select Module. Step 1: Firstly, press ALT + F11 altogether to open up Microsoft Visual Basic window.

Using VBA Macro Code, we can easily remove non-printable characters. Method 4: Apply VBA Macro Code to Delete Non-Printable Characters Read More: How to Remove Non-Alphanumeric Characters in Excel (2 Methods) Then all the characters get removed and the dataset looks similar to the image below. Step 5: Finally, repeat the steps as many times as you need. Click Replace All Non-printable characters get replaced by SPACE characters. Step 3: Afterward, in the Replace With command box, Type Char(32), which is the representation of the SPACE character. Step 2: Then, in the Find What command box, type characters( Char(11), Char(7), etc.) that are present in the dataset. Step 1: Firstly, press CTRL + H altogether. Method 3: Utilize Find & Replace Feature to Remove Non-Printable Charactersįind and Replace feature can find any character printable or non-printable and replace it with whatever character you choose. How to Remove Parentheses in Excel (4 Easy Ways).Remove Apostrophe in Excel (5 Easy Methods).How to Remove Semicolon in Excel (4 Methods).How to Remove Characters from Left and Right in Excel.Read More: How to Remove Non-numeric Characters from Cells in Excel Characters in the reference cell get removed & displayed in the selected cell.

Finding hidden characters in excel