Help needed with text formatting

LordFader · Feb 8, 2023, 9:26 PM

Hi,

can a Notepad++ pro tell me an efficient way/script how to get this output data:

MSCI EM USD (Acc)
IE00BTJRMP35 - Xtrackers
MSCI World Health Care USD (Acc)
IE00BM67HK77 - Xtrackers
AI & Big Data USD (Acc)
IE00BGV5VN51 - Xtrackers

… into this form:
MSCI EM USD (Acc); IE00BTJRMP35; Xtrackers
MSCI World Health Care USD (Acc); IE00BM67HK77; Xtrackers
AI & Big Data USD (Acc); IE00BGV5VN51;Xtrackers

01: “Name”, “ISIN” and the “Issuer” must each be placed on a single line with a “;” between them.

I am curious if anyone has a solution :-)

PeterJones · Feb 8, 2023, 9:34 PM

@Till-Ehrich said in Help needed with text formatting:

I am curious if anyone has a solution :-)

Yes, given certain assumptions about the data. Hope this helps.

…

Oh, wait, let me guess: Next you’re going to tell me you want to know what that solution is. ;-)

FIND = (?-s)$Acc$\K\r\n(\w+) - (.*)
REPLACE = ; $1; $2

----

Useful References

----

Please note: This Community Forum is not a data transformation service; you should not expect to be able to always say “I have data like X and want it to look like Y” and have us do all the work for you. If you are new to the Forum, and new to regular expressions, we will often give help on the first one or two data-transformation questions, especially if they are well-asked and you show a willingness to learn; and we will point you to the documentation where you can learn how to do the data transformations for yourself in the future. But if you repeatedly ask us to do your work for you, you will find that the patience of usually-helpful Community members wears thin. The best way to learn regular expressions is by experimenting with them yourself, and getting a feel for how they work; having us spoon-feed you the answers without you putting in the effort doesn’t help you in the long term and is uninteresting and annoying for us.

Terry R · Feb 8, 2023, 9:36 PM

@Till-Ehrich said in Help needed with text formatting:

I am curious if anyone has a solution

How familiar are you with Regular Expressions (regex)? Using that you can create filters which capture segments of the text. These filters rely on certain characters (or strings) being in the right location on a line, say start or end of line.

So the first filter would capture the 1st line of the 2 line group. Now it could guarantee that it’s the first line it’s capturing if you were certain all those first lines of the groups always contained the ( and ) near the end of the line. Even better if the Acc was inside the brackets. A second filter would capture up to the - in the second line. A third filter would capture the remainder of the second line. Then when writing the 3 captured strings back you would insert the ; character.

Oh wait, I’ll stop here as I see @PeterJones has just provided pretty much what I was saying.

Terry

LordFader · Feb 8, 2023, 9:53 PM

This post is deleted!

LordFader · Feb 8, 2023, 9:56 PM

@PeterJones said in Help needed with text formatting:

; $1; $2

Perfect it works, thank you :-)

The forum here changed the syntax in the post, so it didn’t run at first, but now it does. Super thanks, I will look deeper into the subject.

Alan Kilborn · Feb 8, 2023, 9:59 PM

@Till-Ehrich said in Help needed with text formatting:

The forum here changed the syntax in the post

Yes, because you didn’t follow the rules under “Formatting Forum Posts” for such a question.

PeterJones · Feb 8, 2023, 10:02 PM

@Till-Ehrich ,

I started replying to the deleted post, so I saw you had problems.

The search mode must be regular expression. Sorry that I forgot to say that.

And since I tested with “replace all”, I forgot to mention that with the \K, it will only work with Replace All. So if you tried one-replace-at-a-time, then it won’t replace anything.

Alternatively, you could use three groups instead of two, and get rid of the \K, so that it could work one-at-a-time.

FIND = (?-s)^(.*? $Acc$)\r\n(\w+) - (.*)
REPLACE = $1; $2; $3
SEARCH MODE = Regular Expression

converts

MSCI EM USD (Acc)
IE00BTJRMP35 - Xtrackers
MSCI World Health Care USD (Acc)
IE00BM67HK77 - Xtrackers
AI & Big Data USD (Acc)
IE00BGV5VN51 - Xtrackers

into

MSCI EM USD (Acc); IE00BTJRMP35; Xtrackers
MSCI World Health Care USD (Acc); IE00BM67HK77; Xtrackers
AI & Big Data USD (Acc); IE00BGV5VN51; Xtrackers