Editing a list/ Moving words

Alan Kilborn

Well…I don’t have an alternative method, because I think @astrosofista covered it fine. I was just pointing out some minor things.

The use of \R+ would match this type of data, which you didn’t indicate is needed:

0:44
Video Name A






Subtitles
11:32
Video Name B


Subtitles
10:03
Video Name C
0:36
Video Name D
1:26:58
Video Name E
Subtitles

My point about $1 was that it perhaps is better specified as ${1} because maybe there is a situation (not yours) where there is a 0 following. In that case $10 might not work, but ${1}0 would work.

astrosofista

@Alan-Kilborn

Yes, I noticed it after I posted my first response to OP, too late to remove it. The + symbol doesn’t hurt, but it is unnecessary given the provided example data. Anyway, and on the bright side, I took the opportunity to explain its meaning.

Regarding your second comment, I got used to that symbol, probably because it is the same used in AutoHotkey and since it has no other meanings like \. If I remember correctly, very few times I had to use the expression ${nn} and none recently. Nowadays, I prefer to script or split the expression into successive steps, instead of writing a very complex or long regex, since I noticed the process is faster and the code is easier to maintain.
TL;DR Just don’t use ${nn}.

Alan Kilborn

@astrosofista said in Editing a list/ Moving words:

TL;DR Just don’t use ${nn}.

I think you meant I just don't use ${nn}, not as advice to others not to use it.

It is good advice here on the forum to give to others to use, because they can’t get into trouble with it.

astrosofista

@Alan-Kilborn said in Editing a list/ Moving words:

I think you meant I just don’t use ${nn}, not as advice to others not to use it.

You got what I really meant, but unfortunately that sense got lost in translation. To put it clearly, it is just my personal preference, for the reasons given, and is not intended to be advice for other users.

Mathew888

Could I ask another question regarding this please.

Now that we have the data sorted like this:
0:44
Video Name A (Subtitles)
11:32
Video Name B (Subtitles)
10:03
Video Name C
0:36
Video Name D
1:26:58
Video Name E (Subtitles)
10:27
Video Name F

…I need to transfer this data into two columns within an Excel worksheet.

I am currently using the following process in Excel to import the date from the Notepad++ document:
Data (From Text/CSV)/ Import Data i.e. the relevant Notepad++ document/ Select ‘Tab Delimiter’ - N.B. this creates a single row of data/ Transform Data / Remove Alternate Rows
‘Remove Alternate Rows’ sorts the data into one column at a time i.e. firstly the ‘Video Name’ column (this can then be imported separately) but I then have to repeat the process for the other data column i.e. the ‘Video Length’ column.

In Notepad++, to make this process easier, is there a way to prepare the document so that it could be more easily imported by Excel, so that the Video Name and Video Length data would be separated into the two necessary columns and imported in a single action.

For example could Notepad++ insert the ‘Tab’ in the correct places in order that the Tab Delimiter in Excel would work? If so how would this be achieved please?

I have little experience with either program and I accept that I may be going about this completely the wrong way.

I would be grateful for any advice/ alternatives offered.

Alan Kilborn

@Mathew888

I may be going about this completely the wrong way.

You’re (probably) thinking the correct way.

Could I ask another question regarding this please.

Yes, but… I’ll warn you that you can’t keep asking infinite questions here about data formatting (although 2 hardly qualifies as infinite).

You’ll soon be told to read the documentation and figure out how to do your own transformations. Or to show us what you tried and what didn’t work.

This is simply because this is a discussion site about Notepad++, and data transformation really isn’t a Notepad++ topic.

And because we’ve had trouble in the past with people that don’t want to learn, don’t want to think for themselves, and just want others to provide all of their data transformation needs.

But for now, another freebie:

find: (?-s)^(.+)\R(.+)
repl: ${1}\t${2}

Mathew888

@Alan-Kilborn
Thank you, I take on board what you’re saying.

I was keen to know how the methods were working because I am trying to learn.
It’s just that it’s genuinely a whole new language to me.
I don’t know if there’s a Youtube channel suitable for beginners or something like that.

I’ve just figured out that if I change your last to:
find: (?-s)^(.+)\R(.+)
repl: ${2}\t${1}

it places the Video Length Column before the Video Name Column and that is now perfect.

Once again thank you for your help!

Mathew888

Sorry I meant to say it places the Video Name Column before the Video Length Column and that is now perfect.

PeterJones

@Mathew888 ,

The best way to learn regex is to practice. Play around with data and the regexes you see posted here, and see what happens when you change either the regex or the data, and see how the matching changes; a tool like Notepad++'s Find > Mark is great for such experimentation, because it will immediately show you where the matches are, rather than making you cycle through a bunch of Find Next buttons.

Our FAQ section has an entry on regex resources, which is a good starting place for learning regex.

Alan Kilborn

@Mathew888 said in Editing a list/ Moving words:

I’ve just figured out that if I change your last to:
find: (?-s)^(.+)\R(.+)
repl: ${2}\t${1}
it places the Video Length Column before the Video Name Column and that is now perfect.

Sorry, I didn’t really notice what ordering you wanted the columns in.
But your ability to see the pattern in how the regular expression is working, and adapt it to your need is a key step.
Sadly, a lot of other askers in the past were not able to make that leap.

astrosofista

@Mathew888 said in Editing a list/ Moving words:

…I need to transfer this data into two columns within an Excel worksheet.

You might not be aware of this, but if all you need to do is import the example data from your first post into Excel, you can edit it in a single step, as follows:

Search: ^(\d+:\d+(?::\d+)?)\R(.+)\R(Subtitles)?\R?
Replace: $2(?3 \($3\))\t$1\r\n

Of course, it’s a more complex expression, but doable. Look for details and insights in the docs.

Mathew888

@astrosofista
Thank you for this, I’ll give it a go.

Alan Kilborn

@Mathew888

Combining it all into one larger and more complicated expression is only really necessary if you have a need to do it over and over again. If it is just a one-time need, don’t worry about making it “efficient”.

Neil Schipper

@Mathew888 The folks helping you have deep knowledge of regex (which is a kind of programming language) and are very generous with their time.

Learning the basics of regex and what it can do may serve you very well down the road (not least because you will be in a position to look up complex features).

Nonetheless, this problem is also solved with np++ macros and no use of regex S&R. I achieved (what I take to be) your desired output:

Video Name A (Subtitles)	0:44
Video Name B (Subtitles)	11:32
Video Name C	10:03
Video Name D	0:36
Video Name E (Subtitles)	1:26:58
Video Name F	10:27

with 2 rounds of applying macros I recorded.

To prepare:

In Preferences, Language, Tab Settings, normal (careful, choose what applies to you), ensure ‘Replace by space’ is not checked
In find window, set Search mode = extended, search text = \nSubtitles; do at least one search. (Satisfy yourself that ‘Find’ does what you hope.) Caret to start of file.

First macro finds “Subtitles” at the start of a line (F3), and then does familiar editing to get the text on the prior line and add parenthesis.

After checking (maybe on a small file with a handful of entries) can save the macro and run it “Multiple times”. The very last instance might muck the last entry up, fix with Ctl-z.

Second macro does simple editing: cut text of video duration, delete line, navigate to end of the current line (name, maybe with subtitle), insert tab, paste duration, cursor to start of next entry.

Run macro on the whole file, again maybe fix up last entry.

It can be fun developing and checking macros; there’s much less reference material to plow through. You may need to fuss about various settings before you run it (and change back afterwards to what you prefer in normal editing), and, where you leave the cursor before you stop recording.

Regex’s is a much deeper topic than macros, but the latter can solve lots of problems, and in a more intuitive way.

Mathew888

@Neil-Schipper
Thanks for your info on Macros in this kind of situation, I’ve heard of them but have not used them previously.