• Login
Community
  • Login

Copy and paste within a Macro

Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
18 Posts 4 Posters 1.9k Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • G
    guy038
    last edited by guy038 Apr 14, 2021, 11:37 AM Apr 14, 2021, 11:31 AM

    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

    A 1 Reply Last reply Apr 14, 2021, 11:58 AM Reply Quote 0
    • A
      Alan Kilborn @guy038
      last edited by Apr 14, 2021, 11:58 AM

      @guy038 :

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

      1 Reply Last reply Reply Quote 2
      • G
        guy038
        last edited by Apr 14, 2021, 12:15 PM

        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

        ? 1 Reply Last reply Apr 14, 2021, 12:21 PM Reply Quote 1
        • ?
          A Former User @guy038
          last edited by Apr 14, 2021, 12:21 PM

          @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)

          1 Reply Last reply Reply Quote 1
          • A
            Alan Kilborn
            last edited by Apr 14, 2021, 1:16 PM

            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.

            ? 1 Reply Last reply Apr 14, 2021, 1:29 PM Reply Quote 0
            • ?
              A Former User @Alan Kilborn
              last edited by Apr 14, 2021, 1:29 PM

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

              1 Reply Last reply Reply Quote 0
              • G
                guy038
                last edited by guy038 Apr 14, 2021, 1:48 PM Apr 14, 2021, 1:42 PM

                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

                ? 1 Reply Last reply Apr 14, 2021, 1:54 PM Reply Quote 0
                • ?
                  A Former User @guy038
                  last edited by Apr 14, 2021, 1:54 PM

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

                  You’re my hero!!

                  1 Reply Last reply Reply Quote 1
                  • G
                    guy038
                    last edited by guy038 Apr 15, 2021, 7:31 PM Apr 14, 2021, 2:01 PM

                    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

                    ? 1 Reply Last reply Apr 15, 2021, 3:30 PM Reply Quote 0
                    • ?
                      A Former User @guy038
                      last edited by Apr 15, 2021, 3:30 PM

                      This post is deleted!
                      A 1 Reply Last reply Apr 15, 2021, 5:05 PM Reply Quote 0
                      • A
                        Alan Kilborn @A Former User
                        last edited by Apr 15, 2021, 5:05 PM

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

                        ? 1 Reply Last reply Apr 15, 2021, 5:51 PM Reply Quote 1
                        • ?
                          A Former User @Alan Kilborn
                          last edited by Apr 15, 2021, 5:51 PM

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

                          A 1 Reply Last reply Apr 15, 2021, 6:06 PM Reply Quote 0
                          • A
                            Alan Kilborn @A Former User
                            last edited by Apr 15, 2021, 6:06 PM

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

                            1 Reply Last reply Reply Quote 0
                            • G
                              guy038
                              last edited by guy038 Apr 15, 2021, 6:58 PM Apr 15, 2021, 6:06 PM

                              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

                              ? 1 Reply Last reply Apr 15, 2021, 6:18 PM Reply Quote 0
                              • ?
                                A Former User @guy038
                                last edited by Apr 15, 2021, 6:18 PM

                                @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

                                1 Reply Last reply Reply Quote 0
                                • G
                                  guy038
                                  last edited by guy038 Apr 16, 2021, 10:45 AM Apr 15, 2021, 7:24 PM

                                  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

                                  1 Reply Last reply Reply Quote 0
                                  • gstaviG
                                    gstavi
                                    last edited by Apr 19, 2021, 5:53 PM

                                    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.

                                    1 Reply Last reply Reply Quote 0
                                    11 out of 18
                                    • First post
                                      11/18
                                      Last post
                                    The Community of users of the Notepad++ text editor.
                                    Powered by NodeBB | Contributors