need to replace the pipe with / and remove the quotes in text

Aj Jarrard

need to replace the pipe with / and remove the quotes

Seats||HHSL1X1FH53|"11|635"|C:\Program File

so the outcome is

Seats||HHSL1X1FH53|11/635|C:\Program File

This does the find

"\d+[|]\d+"

I need guidance for how to write the Replace

—

moderator added code markdown around text; please don’t forget to use the </> button to mark example text as “code” so that characters don’t get changed by the forum

PeterJones

@Aj-Jarrard,

"\d+[|]\d+"
I need guidance for how to write the Replace

Good job on finding something that will do the match. Sharing your data and the progress so far makes it much easier to help you.

You will have to tweak the FIND slightly, in order to capture the digits before and after the | . Once you capture those, you reference those capture group valuse in the substitution expression
FIND = "(\d+)[|](\d+)"
REPLACE = $1/$2

----

Useful References

Aj Jarrard

@PeterJones Thank you! That worked perfectly.

Mark Olson

@Aj-Jarrard
For your specific needs, the regex that PeterJones came up with is fine (obviously, since you liked it). But if you are interested in more general-purpose tools that you might find useful, read on.

It looks to me like you have a pipe-separated variables file. If this is true, and if in addition your file has 5 columns and complies with RFC 4180, the following regular expression can recognize a full row of your file, capturing each column of that row as a separate capture group:

(?:\A|(?<=\r\n))(?'column'[^|\r\n"]*|"(?:[^"]|"")*")\|((?&column))\|((?&column))\|((?&column))\|((?&column))(?=\Z|\r\n)

You could customize this regex to match rows where a specific row matched a certain pattern. For example, if you wanted to only match rows where the fourth column matched the pattern "\d+\|\d+", you would simply modify the regex to this:

(?:\A|(?<=\r\n))(?'column'[^|\r\n"]*|"(?:[^"]|"")*")\|((?&column))\|((?&column))\|("\d+\|\d+")\|((?&column))(?=\Z|\r\n)

and you could do things like replace that regex with ${1}|${2}|${3}|COLUMN FOUR|${5} to convert

Seats||HHSL1X1FH53|"11|e "|C:\Program File 14
Seats||HHSL1X1FH53|"11|f "|C:\Program File 15
Seats||HHSL1X1FH53|"11|10"|C:\Program File 16
Seats||HHSL1X1FH53|"11|11"|C:\Program File 17

to

Seats||HHSL1X1FH53|"11|e "|C:\Program File 14
Seats||HHSL1X1FH53|"11|f "|C:\Program File 15
Seats||HHSL1X1FH53|COLUMN FOUR|C:\Program File 16
Seats||HHSL1X1FH53|COLUMN FOUR|C:\Program File 17

The CSVLint plugin is pretty useful in general with delimiter-separated variables files, and the JsonTools regex search form can also be helpful if the files comply with RFC 4180.

Aj Jarrard

Thank you for sharing and giving me an opportunity to learn more. The file actually contains 32 columns, I just cut out those to show the issue I was trying to resolve.

Thomas Knoefel

@Aj-Jarrard
Or you can use MultiReplace Plugin to target specific columns and replace content only in these columns. … and you can even colorize your columns with that Plugin.