Using Notepad++ to automate modification of Excel document because cells contain **

Question

I am working on modification of a translation document that utilizes references for duplicate strings.

I want to run a macro that finds the REF:x,

Sample Table

REF	en-US
REF:1	Acknowledge
	`REF:x`

Expected Result

REF	en-US
REF:1	Acknowledge
	Acknowledge

Selects and copies the word next to it,
Then searches for every instance of **REF:x**
Then replace with the copied word.

I have a macro recorded in Notepad++, that is working on a CSV that is opened there.

My question is how can I automate it to increment x and run it over all ~1500 references. I can't do this in Excel due to the ** in the fields. Searching for ** just yields everything.

If there is a way to complete this within Excel, that would be excellent, but I can't think of anything. I would be open to other solutions as well.

I am just not wanting to manually perform the operation.

I recorded a macro in Notepad++ that does work, but it is specific to a certain number, i.e. REF:1, and I am needing to run this for REF:x on a loop with x increasing.

This is the recorded macro

<Macro name="Translation1" 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="REF:1,"/>
<Action type="3" message="1625" wParam="0" lParam="0" sParam=""/>
<Action type="3" message="1702" wParam="0" lParam="768" sParam=""/>
<Action type="3" message="1701" wParam="0" lParam="1" sParam=""/>
<Action type="0" message="2178" wParam="0" lParam="0" sParam=""/>
<Action type="3" message="1700" wParam="0" lParam="0" sParam=""/>
<Action type="3" message="1601" wParam="0" lParam="0" sParam="**REF:1**"/>
<Action type="3" message="1625" wParam="0" lParam="0" sParam=""/>
<Action type="3" message="1602" wParam="0" lParam="0" sParam="Acknowledge"/>
<Action type="3" message="1702" wParam="0" lParam="768" sParam=""/>
<Action type="3" message="1701" wParam="0" lParam="1609" sParam=""/>
</Macro>

Provide some sample data. You can search for '*' in EXCEL, by just prefixing it with '~' (tilde; '~*~*' for '**'). You can use regular expression with substitution in NotePad++ (and newer EXCEL) — tinazmu
– tinazmu, Commented May 2 at 1:09
Please, edit your question and add sample data and expected result. — Toto
– Toto, Commented May 2 at 7:43
I have edited the question to make it a little bit clearer by using the formatting facilities available. Please check that these changes correctly show what is intended. You can edit the question to make any corrections, and to add the details requested in other comments. — AdrianHHH
– AdrianHHH, Commented May 2 at 8:37
I have added a bit of sample data, but can grab some more. This should give you an idea what I am looking for though. Thank you for your responses, I really do appreciate it. — Mike Benson
– Mike Benson, Commented May 2 at 13:27

Toto · Accepted Answer · 2025-05-03 12:00:30Z

0

Ctrl+H
Find what: ^(REF:\d+),([^,\r\n]+)[\s\S]+?\K\*\*\1\*\*
Replace with: $2
TICK Match case
TICK Wrap around
TICK Regular expression
Replace all

Explanation:

^            # beginning of line
(REF:\d+),   # group 1, REF: followed by 1 or more digits
([^,\r\n]+)  # group 2, 1 or more any character but comma or line break the field that follows REF:
[\s\S]+?     # 1 or more any character including line break, not greedy
\K           # reset operator, forget all we have seen until this position
\*\*\1\*\*   # backreference to group 1 (i.e. REF:digits) surround by **

Screenshot (before):

Screenshot (after):

edited May 3 at 12:00

answered May 2 at 13:50

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Benson May 2 at 16:28

This would have been much simpler to perform. I will remember this.

Toto May 2 at 16:43

@MikeBenson: If it works for you, feel free to mark the answer as accepted, How to accept an answer

Collectives™ on Stack Overflow

Using Notepad++ to automate modification of Excel document because cells contain **

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related