Copy and paste within a Macro

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.