26

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.

Improve this question
3
  • 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
    Commented Dec 21, 2011 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
    Commented Dec 21, 2011 at 3:24
  • 1
    Have a look at this too: 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog
    – danicotra
    Commented Jun 10, 2017 at 8:51

4 Answers 4

Reset to default
32

In Excel the standard line break Alt + Enter character is ASCII 10. From the look of your screenshot it appears 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, i.e., Alt 0010 for just the line break, or Alt 0013 (release 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.

Improve this answer
18

If you press Ctrl + J while in the Find dialog, you will be able to search for those characters.

Improve this answer
4
  • 1
    I see the cursor changes, but what does this do exactly?
    – Louis Waweru
    Commented Mar 16, 2013 at 0:44
  • 1
    +1. Didn't know this. I always used to do a num lock and then alt+0010; this is a quick thing. @Louis: the cursor change was because a new line char[chr(10)] was inserted in the field.
    – Fr0zenFyr
    Commented Sep 17, 2013 at 4:18
  • 1
    Awesome. I am on Parallels on OS X and Alt key combinations don't seem to work. This did the trick!
    – n1000
    Commented Dec 12, 2015 at 14:23
  • It seems like Ctrl + J only finds CR + LF but not just LF (at least in a CSV that contains both types of line breaks).
    – makrom
    Commented Dec 8, 2021 at 16:31
18

If you want to do this with a formula, it would be FIND(CHAR(10),A3)

This is useful if trying to break apart an address block.

Improve this answer
0
6

The mid() function can parse through multiple lines.

Let's say this address is in cell A1:

Google, Inc.
1600 Amphitheatre Pkwy
Mountain View, CA 94043


Let's grab the street address on the second line.

The first step is determine the position number of the two return characters.

The first return character is found at '13' with this: 

=SEARCH(CHAR(10),A1)


The second return character is found at '36' with this: 

=SEARCH(CHAR(10),A1,SEARCH(CHAR(10),A1)+1)



Now the second step. We need to return our address text between the two character counts 13 & 36.

Here the formula is nested together:

=MID(A1,SEARCH(CHAR(10),A1),SEARCH(CHAR(10),A1,SEARCH(CHAR(10),A1)+1)-SEARCH(CHAR(10),A1))
Improve this answer
1
  • 1
    After 10 years somebody have to thank you for this answer. So, Thanks!!
    – M. Chris
    Commented Jul 6, 2023 at 7:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .