Copy and paste within a Macro



  • 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



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

    You’re my hero!!



  • 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



  • This post is deleted!


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



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



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



  • 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



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



  • 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



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


Log in to reply