Hope you find the above discussed methods super easy to use. 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. Step 6:Drag the Fill Handle & It removes the rest of the cell’s non printable characters.ĬLEAN, SUBSTITUTE functions remove non-printable characters with ease. Step 4: Go to the worksheet & Type =RemoveNP(C3) in any cell ( D3). Then using RemoveNP Function, characters get replaced as string ( ’’ ’’). Here, we’ve declared characters that may be existing in our dataset or exact characters ( Char(7), Char(11), etc.) exist in our dataset.

In the code, we declared a function RemoveNP with two potential parameters. MText = mWF.Substitute(mText, MyText(J), mSubText) MText = mWF.Substitute(mText, Chr(I), mSubText) Step 3: Paste the code below in the Module Function RemoveNP(mText As String, Optional mSubText As String = ' ') Step 2: From the Toolbar, Click Insert & Select Module. Step 1: Press ALT+F11 altogether to open up Microsoft Visual Basic window. Using VBA Macro Code we can easily remove non-printable characters. Read more: How to Remove Blank Characters in Excel Method 4: Using VBA Macro Code Then all the characters get removed and the dataset looks similar to the image below

Repeat the steps as many times as you need. Click Replace All Non-printable characters get replaced by SPACE characters.

Step 3: In the Replace With Command Box, Type Char(32), which is the representation of the SPACE character. Click Find All Find What Characters existing in the dataset will appear. Step 2:In the Find What Command Box, Type Characters( Char(11), Char(7), etc.) that are present in the dataset. Read more: How to Remove Special Characters in Excel Method 3: Using Find & Replaceįind and Replace feature can find any character printable or non-printable and replace it with whatever character you choose. Characters in the reference cell get removed & displayed in the selected cell. Step 1: Insert =SUBSTITUTE(C7,” “,””) in any cell ( D7). Formula, ( ” “) is replaced character and ( “”) replaces the character.

Finding hidden characters in excel