up vote 1 down vote favorite
share [g+] share [fb]

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially: newline characters

If I hit F2 (to edit) then Enter (to save changes) on each of the cells with a newline (without actually editing anything), Excel automatically changes the layout to look like this: enter image description here

I don't want these newlines characters here, as it messes up data processing further down the track. How can I do a search for these to detect more of them? The usual search function doesn't accept an enter character as a search character.

link|improve this question
100% accept rate
Are you able to post a sample workbook somewhere online? Removing linebreaks is possible using Find, or VBA, but it would be good to see exactly what the characters are, and test the replacement works as you desire – brettdj Dec 21 '11 at 2:59
If you could provide an answer for finding the linebreaks that can be inserted by typing alt-Enter while editing a cell, I think that would solve it. If not, I might be able to find a way to put the worksheet online later. Thanks! – Highly Irregular Dec 21 '11 at 3:24
feedback

1 Answer

up vote 2 down vote accepted

In excel the standard line breack (alt-enter) character is ascii 10. From the look of your screen shot it epear there is another character, probably ascii 13.

To find these, in the standard Find (or Replace) dialog, in the Find What field, use the standard method of entering ascii character codes: hold down Alt and type (on the numeric keypad) the character code, ie Alt 0010 for just the line breack, or Alt 0013 (replease Alt and press again) Alt 0010

If that extra character is not a ascii 13, you can use the function =Code(AString) to get the code of the left most character of a string

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.