Editing a list/ Moving words

Mathew888

I want to edit a long Notepad++ list containing Video Recording details in order that it transfers correctly into a worksheet.

The document may contain 1000’s of random video titles but an example might be:

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
etc etc

In the above example:
Video A is 0:44 in length and does have Subtitles.
Video B is 11:32 in length and does have Subtitles.
Video C is 10:03 in length and has NO Subtitles.
Video D is 0:36 in length and has NO Subtitles.
Video E is 1:26:58 in length and does have Subtitles.
Video F is 10:27 in length and has no Subtitles.

I would like the above list to be edited to read:

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
etc etc

Any help would be appreciated thanks.

astrosofista

@Mathew888

It’s a simple task for RegEx. Open the Replace dialog (Ctrl + H) and type:

Search: \R+(Subtitles)
Replace: \x20\($1\)

Put the caret at the very beginning of the document, select the Regular Expression mode and click on Replace or Replace All.

Hope this helps.

Mathew888

Brilliant! Your instructions work perfectly as intended. Thank you.
As I’m quite new to Notepad++ and know very little about the code you have described would you mind explaining how the coded instructions are achieving the result please, in other words the steps it is taking?

Ekopalypse

@Mathew888

here you can find the Npp documentation about regular expression syntax. Have fun :-)

astrosofista

@Mathew888 said in Editing a list/ Moving words:

would you mind explaining how the coded instructions are achieving the result please, in other words the steps it is taking?

Sure, here is my attempt. If something is not clear enough, try to read the docs, as @ekopalypse suggested you.

In the searching field: \R+(Subtitles)

\R represents one or more carriage returns \r or line feed \n.
+ indicates that there could be one or more CR/LF.
() symbols for capturing groups, which will be stored in a predefined variable $n, where n is for any integer.

In replacement: \x20$$1$

\x20 represents a space character. This symbol is used because chances are that a traditional space could be unintentionally missed in a copy/paste operation.
 since parentheses are reserved symbols, they should be escaped with a backslash \ to get their usual meaning.
$1 represents the first captured group, in this case the string Subtitles.

Take care

Mathew888

Thanks for all your help!

Alan Kilborn

@astrosofista

Just a couple of comments:

I’m not sure why you’d use \R+ here, instead of \R. OP didn’t say anything about blank lines.
I like your use of $1 instead of everyone’s favorite of \1 – however, I like the more verbose ${1} because if people get trained to write it that way (yes, it is TWO more keystrokes) then when the need for more than 9 groups arise, everything still works. BTW, \1 is “okay” but it sometimes generates questions because \ is an escape character.

Mathew888

@Alan-Kilborn
Would you be kind enough to provide your alternative in full please?
Method 1 works well but I’m just interested to see if I notice any difference with your method.
Hopefully I may understand this a little better in the future.

Alan Kilborn

@Mathew888

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.