8

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.

Problem I face in Notepad++

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
  • Edit menu: I tried Remove Empty Lines from Edit menu.*3
  • Edit menu: I tried Join Lines from Edit menu.

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?...

9
  • prolly a dumb question, but did you change the search mode when you tried the /r/n combinations? I just tried with some dummy text copied from excel and it seemed to work
    – mcalex
    Jun 19, 2013 at 8:40
  • Hmm... you mean normal vs. extended and reg. expression? It's set to Extended.
    – Samir
    Jun 19, 2013 at 8:44
  • I just tried typing in 6 rows in Excel. From A1 to A7 I typed in 1234 to 1240. And from B1 to B7 I typed in "something" repeatedly. Then I tried copy and paste it in Notepad++. It is as you said, it does work. I think the problem is that the data I have in Excel is copied and pasted there from a Wikipedia table/list. Some of the cells where automatically joined to one, so I had to select everything and click the button that undoes this so that every entry has it's own cell. Somewhere along the way tabs where introduced and that's the problem.
    – Samir
    Jun 19, 2013 at 8:51
  • 1
    @pnuts What is "tl;dr"? Is that Latin?...
    – Samir
    Jun 19, 2013 at 10:08
  • 1
    Oh my! Would you look at that! :) LMAF! I thought you just came up with that. But it really is an Internet acronym! "Really translates to: I'm too lazy to read the entirety of what you said, but I still want to say something." Helpful for others you say? You're telling people to stop reading. Right at the beginning! If they feel the same way as you are, they won't even read your semi-colon. I'm not sure they can endure reading a single dot mark. ROFLAPMP!
    – Samir
    Jun 19, 2013 at 11:06

5 Answers 5

8

Another response after the update...

If the text is in this format you should be able to do a find and replace using the following:

Set the search mode in Notepad++ to "Extended" first.

Set "Find What" to "\t\r\n" and "Replace With" left blank.

That should search and replace for the pattern TAB CR LF as in the text above.

9
  • I got some good tips from Zakinster, but this method is by far the easiest. No need to switch to reg. expression mode and pay close attention to syntax.
    – Samir
    Jun 19, 2013 at 9:26
  • @Sammy This method is indeed simpler, but it only works because you always have only one tab in your empty line. The regex approach would be able to handle any kind of empty lines.
    – zakinster
    Jun 19, 2013 at 9:39
  • @zakinster True! But it's a much steeper learning curve to learn and understand regular expression. And besides, \t\r\n does answer directly to my particular problem. But I'm always happy to learn new things. So do you have any good guide I can use to learn reg. expression in Notepad++? How did you learn it? And is there really more than one kind of empty line? Can you elaborate a bit on that? I always thought that an empty line only had the hidden characters CR and LF.
    – Samir
    Jun 19, 2013 at 9:57
  • 2
    @Sammy Since regex are not specific to Notepad++, you can learn it anywhere (regexone.com seems to be a nice starting point). You should also take a look at what kind of regex Notepad++ actually support.
    – zakinster
    Jun 19, 2013 at 10:05
  • 1
    @pnuts Yes, this seems to be the case. Here is the release note for 6.0. They point out that PCRE is one of the new features. I'll guess they had regex support prior to version 6.0 but they used different syntax and characters. Everyone seem to be comparing to Perl when they talk about reg. expressions. So I guess that's the closest it gets to a common (de-facto) standard for regex.
    – Samir
    Jun 19, 2013 at 14:46
7

You can use the Regular Expression mode of Search->Replace to do this :

Your empty line can be described with the regular expression \s*\R following a new line \R, thus you could use the following replacement :

Find what : "(\R)(\s*\R)+"
Replace with : "\1"

\R is a universal new-line, it's equivalent to \r\n (Windows), \n (Unix) or \r (Mac), depending on the current new-line format.

\s is any spacing character, it's equivalent to [[:space:]] or [ \t\n\r\f\v]

9
  • Thanks for the edit! I tried the first replacement schemes you posted and some of them didn't work. I think the third one moved everything to one line. But this new one works. It does what it's supposed to. Maybe you should also point out that search mode must be set to reg. expression and not the Extended mode.
    – Samir
    Jun 19, 2013 at 9:18
  • I see you keep editing the post, the one I meant was \r\n([\t]*\r\n)+ in Find, and then \r\n in Replace, and search mode set to regular expression. That's the one that did work.
    – Samir
    Jun 19, 2013 at 9:21
  • 1
    True! This new incarnation does what it's supposed to. For the record, that would be (\R)(\s*\R)+ in "find what" and \1 in "replace with". Just in case you decide to change your mind again! ;)
    – Samir
    Jun 19, 2013 at 10:03
  • 1
    @pnuts Haha! Nice! That would be Fastest Gun in the West.
    – Samir
    Jun 19, 2013 at 11:32
  • 1
    @pnuts How is SCITE relevant to that mod post by Oliver? You mean because he refers to the other answers in his own answer?... but I don't think he copied it straight off. That post by Oliver is sort of a hybrid between SCITE and FGIW. But you don't have to look far to find a SCITE posting. Just saying... putting it out there. ;)
    – Samir
    Jun 19, 2013 at 14:56
3

In Excel (I'm using 2010) you can select and remove blank rows:


Select your data range

starting data


Under the Home tab go to Find & Select and choose Go to special

Go to special


In the options window choose blanks. This will select all blank cells within the data range. Right click within a blank cell and select delete, then select Move cells up

selected blanks


Done

3
  • Thank you! This will come in handy the next time I face this type of problem in Excel. But learning to use Notepad++ is always good, because you can do so much more in Notepad++ when it comes to manipulating data strings.
    – Samir
    Jun 19, 2013 at 15:04
  • Do you know if there is any "count" option in Excel? A window or dialog box of some kind that can display stats like number of rows, number of columns, total number of cells, cells with data, number of characters in the current workbook or sheet? Maybe even a size counter in Megabyte (MB)? The only way to tell the size of an Excel document that I know of is by looking at the properties of the file in Windows. So something like this would be nice. Correction: In Excel 2010 there is the Info tab under File menu option. It shows file size. But nothing more.
    – Samir
    Jun 19, 2013 at 15:08
  • I do love Notepad++ Firstly, for small analysis you can select a data set and look at the calculations next to the zoom bar at the bottom of the screen. For large scale analysis I don't think there's anything built in available for <=2010, but 2013 has a Workbook Analysis function in the Inquire Add In that's pretty damn awesome!
    – CLockeWork
    Jun 19, 2013 at 15:15
2

If you don't care about retaining the order of the lines you could sort the lines and this will either put the empty lines at the top or the bottom of the document depending on which sort order is used?

1
  • 1
    Thanks! Going to TextFX Tools menu, and then "Sort lines case insensitive" joins the lines so that the empty lines are on top. This gives me something I can work with. But what if retaining line order is important? And is there a menu option to sort lines in Notepad++ without the TextFX plug in? Also, see my update to the question. Maybe you can give me another solution?
    – Samir
    Jun 19, 2013 at 8:35
2

For Notepad++, by far the easiest way to deal with this situation is a built-in function:

Edit > Line Operations > Remove Empty Lines (Containing Blank characters)

@Sammy said that he tried the option immediately above this one ("Remove Empty Lines"), but you need to choose the one including lines with blank characters (such as Tab and Space).

I love the Find/Replace features of Notepad++, but this built-in function is easier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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