Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.