How to remove special characters from Excel cell

Enlarge it to clearly view special character

Whenever you link cell in Excel worksheet to a cell that has multiple lines, you might notice that the linked cell shows text in single line and a tiny suspicious character shows up at the place of line break.



When you add a line break in source cell, Excel than adds an special character for that line break. That's the same suspicious character that we are seeing in this screenshot. You can fix this issue by simply enabling the "Word Wrap" option which will convert this symbol into line break and move the remaining text into next line. Now in this example we cant use this solution because we need to show all the text in one line and we just don't need this creepy character to mess it up.

We can easily remove such odd characters by using Excel's Clean function.

1. Select the linked cell. In my case, its formula bar is showing ='Edit Header'!B21 where "Edit Header" is the sheet name where the source cell "B21" is located.
2. Now press "F2" key to enter cell edit mode or simply double click this cell.
3. Edit the formula by adding a function =CLEAN('Edit Header'!B21)

"Clean" is Excel function which removes any non-printable characters from the cell.
Apply this function on all the linked cells and that's it. :)

This function would also work for the linked cell for Multi-line Text Box.

If you liked this solution, spread it to your friends.
If you need any help on Excel, just type it as a comment and i will give you solution at my earliest.