1

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!

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
0

=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

Improve this answer

You must log in to answer this question.