# How to remove empty lines in Notepad++ after pasting in data from Excel

I want to clean up some data I copied from an Excel spreadsheet and wash off any formatting using Notepad++. I want to take advantage of line numbering of Notepad++ to count the number of lines I have. That is: the number of rows with actual data in Excel.

Cause for exporting data from Excel

Each row of a spreadsheet is it's own line number. It's indicated by the number in the left margin in Excel. So I could look at that and figure it out.

That is, if it looks like this:

  A    B
1 #### something
2 #### something
3 #### something
4 #### something
5 #### something
6 #### something


But if it looks like this…

  A    B
1 #### something
2 #### something
3
4 #### something
5
6 #### something


… and the list is very long, then it's really hard to say how many rows/lines there are in this list. I would have to go down the list and visually count the rows. I mean I know Word has the option to tell you how many lines or words you have in a document. But I'm not sure there even is such a thing in Excel.

And I don't know if you can do anything about this right in Excel, i.e. tell it to remove rows of cells without any data and move the rows of cells next to each other (top/bottom). If you know of a way, please share it. But I think Notepad++ is a great tool for manipulation of text data like this.

I essentially want to remove the extra line breaks. This is what it might look like.

Line 01:something
Line 02:something
Line 03:something
Line 05:
Line 06:
Line 07:something
Line 08:something
Line 09:
Line 10:
Line 11:
Line 12:
Line 13:something


I want to remove line 5, 6, 9, 10, 11, and 12.

What I have tried

• TextFX Edit: I tried the option Delete Blank Lines from Edit menu. Didn't
work.*1
• Replace: I tried typing \n\r in find and nothing in replace
field. "0 occurancies were found" *2
• Replace: I tried typing \r\n in find and nothing in replace
field. "402 occurancies were replaced" But it puts everything on a
single line. Like this: something something something

So what else?… what else can I try?

This is the result I am after:

Line 01:something
Line 02:something
Line 03:something
Line 07:something
Line 08:something
Line 13:something


How can I achieve this?

Update

****1, 2, 3** = These three methods should work. I have tested and verified each. The other two do not and should not. They have a different purpose.*

Okay, I think I know now why this is not working. The text I am processing is copied and pasted from Excel 2010 into Notepad++ and there are some extra characters in there that throw off these commands. There are some tabs and CF or LF characters.

Here is a closer description of what I have.

Line 01:####[tab]something[CR][LF]
Line 02:####[tab]something[CR][LF]
Line 03:####[tab]something[CR][LF]
Line 05:[tab][CR][LF]
Line 06:[tab][CR][LF]
Line 07:####[tab]something[CR][LF]
Line 08:####[tab]something[CR][LF]
Line 09:[tab][CR][LF]
Line 10:[tab][CR][LF]
Line 11:[tab][CR][LF]
Line 12:[tab][CR][LF]
Line 13:####[tab]something


Where…

• = 4-digit number
• [tab] = tab
• [CR] = carriage return
• [LF] = line feed

So how do you deal with this?…