strange character appears

[skalaima]

I export data from an external program into excel and what is a carriage return in the external program is showing up in excel as "□" (actual version is slightly bigger, what you see here is a "white square" from insert symbol in excel).

The strange part is I can't "re-create" it even though it's visible in excel as "□". When I copy it and try to paste it in excel I get a blank space. When I paste it in MS Word I get a carriage return (manual line break). My goal is to replace all occurences of this strange character with manual line breaks (^l) using find and replace.

Please help...

 

[shg]

=clean(a1) will remove them. Then you can copy the formula results and paste values over the original data.

 

[skalaima]

Thanks for the suggestion, but that doesn't quite get me what is needed. Using clean would essentially replace the "□" with a blank space. What I'm looking for is a way to replace that character with a carriage return so the text following it is on the next line.

 

[shg]

Actually, CLEAN replaces it with nothing.

If you want to replace the carriage return with a line feed,

=substitute(A1, char(13), char(10))

Then you need to format the cell to wrap text.

 

[skalaima]

That formula would work, if excel would recognize the carriage return. That's where the problem lies though, even though the carriage return (strange looking character) appears in excel it's not being recognized so when I try the substitute formula it doesn't change the contents of the cell because it doesn't pick up the carriage return.

I think this is a unique issue because the data is being imported from an external program and excel is substituting that funny symbol for all carriage returns.

This issue has been driving me crazy all day, any other ideas? (cool formula by the way... didn't know about it, thanks!)

 

[shg]

So it's not a carriage return.

Delete all the characters prior to it, and in an adjacent cell, =CODE(A1)

What's that return?

 

[ZVI]

Seems that those symbols are line feed or CHR(10) itself.
Try: Select cells - Right click - Format cells – Alignment tab – Wrap text - Ok

 

[skalaima]

shg: =CODE(A1) gives me "11". Must be a good thing that it actually returned a character value, I was worried it was going to return nothing .

ZVI: Thanks for your suggestion, but all the cells are already wrapped. I tried to remove the wrap then select wrap again but that didn't fix the problem.

 

[skalaima]

=SUBSTITUTE(A1, CHAR(11), CHAR(10))

solves the problem. shg your the man! Thanks for your help.