Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Part I - I have a spreadsheet that was from data exported from an application. When I open it, I can see the 'boxes' that usually are from a line break or paragraph break in a cell. IF I view the spreadsheet in my version of excel 2003, I can see them just fine and know where to look to get rid of them, and when they are gone. If I open it on another PC in Excel 2007, I can not see them to even know they are there to cause the problem.

Q - is there a way to view these boxes, this code, in Excel 2007?

Part II - sometimes, I can use a formula to scrub out these boxes, but sometimes the formula does not remove all. Any advice on how to make sure I get them all? Hard to see sometimes in a spreadsheet with over 5,000 rows of data.

Thank you!

share|improve this question

migrated from stackoverflow.com Aug 20 '09 at 17:35

This question came from our site for professional and enthusiast programmers.

CLEAN should remove all funny characters below 31.

See also Microsoft's Remove spaces and nonprinting characters from text and Top ten ways to clean your data.

share|improve this answer

=substitute(substitute(a1,char(13)," "),char(11)," ") should get rid of carriage returns and line breaks and replaces them with spaces. If you have any other special characters use =code to find out their ascii values and add a substitute for them

share|improve this answer

You must log in to answer this question.