Copy and paste within a Macro

A Former User

I have several hundred files in the following format (pssst - not my actual data):

Leicester
Dog and Duck
Pub
Beefy Cow
Steakhouse
Snail and Lettuce
Pub
Dukes Head
Hotel

I’m trying to convert them into the following format (So I can copy into Microsoft Excel)

Leicester Dog and Duck Pub
Leicester Beefy Cow Steakhouse
Leicester Snail and Lettuce Pub
Leicester Dukes Head Hotel

What I’ve been trying to do in a Macro is

a) Copy First Line to Last Line b) Remove empty 1st Line
c) Merge each two lines into one (with a TAB between)
d) Copy the last line and paste at the beginning of each line

I’m having problems with Copying and pasting - is there an alternative way to get to the outcome I’m looking for?

Many thanks - Steve

guy038

Hello, @mrliskatraz and All,

Just do it with regular expressions ! Quicker and more easy ;-)) So :

Place the caret at the very beginning of your list of fields
Open the Replace dialog ( Ctrl + H )
- SEARCH (?-s)^(.+)\R(.+)\R(.+)
- REPLACE \1\t\2\t\3
- Untick all square box options
- Select the Regular expression search mode
- Click on the Replace All button

=> Et voilà ! Just paste this modified text in an Excel empty sheet !

Now, you can record this S/R as a macro, as well !

Best regards,

guy038

Alan Kilborn

@guy038 :

Hmm, that doesn’t quite do it; note that Leicester is supposed to lead off every line.

guy038

Hi @mrliskatraz, @alan-kilborn and All

Oh…, I’m really sorry, @mrliskatraz ! Thanks to Alan and his piercing gaze, like an eagle, I see, now, my glaring error !

Now, some questions :

How many couples Dog and Duck - Pub, Beefy Cow - Steakhouse must be preceded with the word Leicester ?
In case of several common leading words, how to identify them ? Are they preceded with a blank line or else ?

BR

guy038

A Former User

@guy038 said in Copy and paste within a Macro:

Hi @mrliskatraz, @alan-kilborn and All

Oh…, I’m really sorry, @mrliskatraz ! Thanks to Alan and his piercing gaze, like an eagle, I see, now, my glaring error !

Now, some questions :

How many couples Dog and Duck - Pub, Beefy Cow - Steakhouse must be preceded with the word Leicester ?

In case of several common leading words, how to identify them ? Are they preceded with a blank line or else ?

BR

guy038

Thanks Guy…

The number of couples varies from file to file… some may have just one couple, another may have 20.

And if it’s relevant, it’s not always Leicester - it is however always the whole contents of the first line! (No blank lines)

Alan Kilborn

Thanks to Alan and his piercing gaze, like an eagle,

Well, I don’t know about that! :-)

The number of couples varies from file to file… some may have just one couple, another may have 20.

This seems rather vague, to me at least.

A Former User

Apologies, I shall try rewriting my post later, so as to make it less vague…

guy038

Hello, @mrliskatraz, @alan-kilborn and All

IMPORTANT : You must use, at least, N++ v7.9.1 or a later version for a correct replacement process !

I’ve found out a regex expression which :

Deletes the first line and rewrites it at the very end of current file, after a first click on the Replace All button
Reorganize all the subsequent lines in couples, preceded by the initial and rewritten line, after a second click on the Replace All button
The 3 fields, of each line, are separated with tabulation characters

So :

Open the Replace dialog ( Ctrl + H )
- SEARCH (?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+
- REPLACE (?1\2\t\1\t)(?3\5\t\3\t\4)
- Tick the Wrap around option
- Select the Regular expression search mode
- Click TWICE on the Replace All button ( Note that, in addition, any subsequent click on the Replace All button does nothing ! )

So, from the example :

Leicester
Dog and Duck
Pub
Beefy Cow
Steakhouse
Snail and Lettuce
Pub
Dukes Head
Hotel

you should get the expected text :

Leicester	Dog and Duck	Pub
Leicester	Beefy Cow	Steakhouse
Leicester	Snail and Lettuce	Pub
Leicester	Dukes Head	Hotel

Now, I created the corresponding macro as I wanted to verify if a same regex S/R, run two times, consecutively, would work ! No problem ! It work like a charm, and especially when the first line is changed ;-)

        <Macro name="Test" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="(?1\2\t\1\t)(?3\5\t\3\t\4)" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="(?1\2\t\1\t)(?3\5\t\3\t\4)" />
            <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
        </Macro>

As you can see, after the first definition line, this macro contains two identical blocks which process :

The first search alternative (?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$, associated with replacement (?1\2\t\1\t), for the first block
The second and third search alternative (?-s)(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+, associated with replacement (?3\5\t\3\t\4), for the second block

Best Regards,

guy038

A Former User

@guy038 Thank you, thank you, thank you! That works perfectly, its been bugging me for weeks!

You’re my hero!!

guy038

Hi, @mrliskatraz,

Just a clarification :

The last line of all your file(s) must end with a line-break. Anyway, if a last line just ends with text bla blah, this overall regex will not match anything ;-))

But, on the other hand, your files must not have pure empty lines at their end, too :-(

Now, if, for instance, you must have 4 fields, preceded by the keyword, in Excel, use this regex :

    - SEARCH    (?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)\R(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+
Groups -->             1                 2                  3     4     5     6              7

    - REPLACE   (?1\2\t\1\t)(?3\7\t\3\t\4\t\5\t\6)

So, from this initial text :

Leicester
Dog and Duck
Pub
aaaaa
11111
Beefy Cow
Steakhouse
bbbbb
22222
Snail and Lettuce
Pub
ccccc
33333
Dukes Head
Hotel
ddddd
44444

You would obtain :

Leicester	Dog and Duck	Pub	aaaaa	11111
Leicester	Beefy Cow	Steakhouse	bbbbb	22222
Leicester	Snail and Lettuce	Pub	ccccc	33333
Leicester	Dukes Head	Hotel	ddddd	44444

As you can see :

In the second alternative, of the search, you must add some (.+)\R parts
In the second part of the replacement you must change the groups to be rewritten, as indicated above ( Besides, note that the last group represents the first word, common in all the rows of your table )

BR

guy038

A Former User

This post is deleted!

Alan Kilborn

@MrLiskatraz said in Copy and paste within a Macro:

How do I use Regex in Excel?

Such a question is off-topic for a Notepad++ forum.

But…the reason that Notepad++ and Excel regular expressions work differently is that is no standard for regex. Well… actually there are standards…just too many of them!

Notepad++ uses the Boost regex engine.
Don’t know what Excel uses (and don’t care because this isn’t an Excel forum).

Perhaps a bigger question is: Why are you trying to work with your data in two different ways? You probably should choose one way and stick with it.

A Former User

@Alan-Kilborn My apologies… I shall look for a more general forum, I didn’t mean to upset anyone. Sorry once again.

Alan Kilborn

@MrLiskatraz said in Copy and paste within a Macro:

My apologies… I shall look for a more general forum, I didn’t mean to upset anyone. Sorry once again.

No problem.
Nobody’s upset.
You didn’t know, before asking.
The main reason to avoid off-topic things is (a) people here probably won’t have the knowledge to answer anyway, and (b) people don’t want to see off-topic discussion eating the bandwidth here.

guy038

Hi, @mrliskatraz and All,

Algouth I’m using very old versions of Word, Excel and others, due to my old XP laptop, I’m sorry to tell you that the general philosophy of Excel and Notepad++, regarding text manipulations, are completely different !

Excel considers actions on cell’s contents and can also manage relations between all of them
The regular expression’s search mode, in Notepad++, generally considers physical line’s contents and sometimes all file’s contents, as the entities to manage

For instance, if we imagine that your initial text, below, is inserted in an Excel sheet :

     A
1 Leicester
2 Dog and Duck
3 Pub
4 Beefy Cow
5 Steakhouse
6 Snail and Lettuce
7 Pub
8 Dukes Head
9 Hotel

I suppose that you could say, with Excel functions, something like :

For any cell (A, even row)
'    So cell (A 2), cell (A,4), cell (A,6)...
  Copy cell (A,1) in cell (B, current even row / 2)
  copy cell (A, current even number) in cell (C, current row / 2)
  copy cell (A, current even number + 1) in cell (D, current row /2 )
End
Delete column A

Which is, obviously, a completely different language than the regexes language !

So, either :

You modify your list, from within Notepad++ with regexes. Refer here and then you paste the result in Excel
You insert your raw text in Excel and you learn how to use the Excel functions and macros. There are plenty of sites devoted to basic and advanced Excel skills ! Just do a Google search of VBA ( Visual Basic for Applications )

Note that, in both cases, this will require a certain investment on your part ! And, sorry, but I doubt that it is possible to mix these two syntaxes, anyway :-(

Best Regards,

guy038

A Former User

@guy038 I think I misunderstood a previous response:

Now, if, for instance, you must have 4 fields, preceded by the keyword, in Excel, use this regex :

- SEARCH **`(?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)\R(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+`**

Is for NPP?

I don’t wish to mix the two syntaxes, I’m happy enough to try and learn NPP - I just got the wrong end of the stick.

Apologies - You’ve been more helpful than I could have expected.

Steve

guy038

Hi, @a-former-user,

Yes !! That all concerns Notepad++, only ! So, as I said before :

Open Notepad++
Copy the text, below, in a new N++ tab ( Ctrl + N )

Leicester
Dog and Duck
Pub
aaaaa
11111
Beefy Cow
Steakhouse
bbbbb
22222
Snail and Lettuce
Pub
ccccc
33333
Dukes Head
Hotel
ddddd
44444

If the last line 44444 does not end with a line-break, hit the Enter key to add one !
Open the Replace dialog ( Ctrl + H )
- SEARCH (?-s)\A([^\t\r\n]+)\R(?s)(.+\R)(?!\t)$|(?-s)(.+)\R(.+)\R(.+)\R(.+)(?=(?s:.+\t(.+)\t\z))|^\t.+
- REPLACE (?1\2\t\1\t)(?3\7\t\3\t\4\t\5\t\6)
- Tick the Wrap around option
- Select the Regular expression search mode
- Click TWICE on the Replace All button ( Note that, in addition, any subsequent click on the Replace All button does nothing ! )


You will obtain this output :

~~~diff
Leicester	Dog and Duck	Pub	aaaaa	11111
Leicester	Beefy Cow	Steakhouse	bbbbb	22222
Leicester	Snail and Lettuce	Pub	ccccc	33333
Leicester	Dukes Head	Hotel	ddddd	44444

Copy these 4 lines in the clipboard ( Ctrl + C )
Open Excel
Open a new empty sheet
Paste the clipboard’s contents ( Ctrl + V ) in cell A1

You should get a table of 4 rows × 5 columns, with the word Leicester in the first column of any row ;-))

Best Regards

guy038

gstavi

My macro:
stand on beginning of Leicester
start recording
shift-end
ctrl-c
end
space
delete
end
space
delete
home
down arrow
ctrl-v
end
enter
up arrow
stop recording

replay recording
replay recording
replay recording
replay recording
replay recording
…

end up with extra Leicester to delete.

A simpler approach would be to just merge every 2 lines and then add the common prefix with column editing.