Notepad++ need to create line breaks with condition variants
Solved
Lyne
-
maclesglyne Posted messages 11 Status Membre -
maclesglyne Posted messages 11 Status Membre -
Hello everyone,
I have a file with commands, each command starts with " followed by 5 numbers and the quotation mark is closed.
Example of the writing of a command "32564".
I have line feeds at the end of each line but a command can span one, two, or three lines.
So I would like to have a code that allows me to say every time you see 5 numbers in quotation marks, you insert a line break on the previous line.
This would allow me to have a line break between each command and consolidate them in Excel.
Thank you all
Céline
I have a file with commands, each command starts with " followed by 5 numbers and the quotation mark is closed.
Example of the writing of a command "32564".
I have line feeds at the end of each line but a command can span one, two, or three lines.
So I would like to have a code that allows me to say every time you see 5 numbers in quotation marks, you insert a line break on the previous line.
This would allow me to have a line break between each command and consolidate them in Excel.
Thank you all
Céline
5 réponses
It is surely possible to skip this step. In Excel, we could imagine a column with a formula that determines whether it is a header or detail row
=IF(AND(MID(A1,1,1)=""""",ISNUMBER(VALUE(MID(A1,2,5)) ),
MID(A1,7,1)="""""),"Header","Detail")
Hello again,
sorry for the late reply but I had a family issue.
Thank you so much for your help :)
So in Excel, the csv file doesn't have any " " surrounding the order number
so I can maybe tell him if it starts with 5 digits then... but I can't find anything on the forums.
sorry for the late reply but I had a family issue.
Thank you so much for your help :)
So in Excel, the csv file doesn't have any " " surrounding the order number
so I can maybe tell him if it starts with 5 digits then... but I can't find anything on the forums.
Your .csv file, if you want to use it with a comma as a field separator, needs to be renamed to .txt
open Excel, file/open the .txt
select file with separator, choose the comma. This way you have columns.
To differentiate the header row and the following rows, you add an empty column in A, and in A2 you put the following formula
open Excel, file/open the .txt
select file with separator, choose the comma. This way you have columns.
To differentiate the header row and the following rows, you add an empty column in A, and in A2 you put the following formula
=IF(ISNUMBER(CNUM(B2));"Header";"******")which you duplicate across all the following rows.
Oh yes, great idea... it works! However, I just have one concern with this method.
You see, in Notepad, the advantage I had before was that there was CR + LF when there was a line break after each command and LF when the command was split across multiple lines.
So I used the search and replace function (I copied the LF and replaced it with nothing), which allowed my CSV to automatically format one line per command, and I would go through Excel using the convert function by comma separator, and... I had an usable file in two clicks.
Now with your solution (which is already great, thank you :)), once the lines are differentiated between the header and the data rows, would you have a way for them to line up automatically? Because when I process a full month, I can have 5 to 6000 Excel lines... doing it manually is going to be tough...
You see, in Notepad, the advantage I had before was that there was CR + LF when there was a line break after each command and LF when the command was split across multiple lines.
So I used the search and replace function (I copied the LF and replaced it with nothing), which allowed my CSV to automatically format one line per command, and I would go through Excel using the convert function by comma separator, and... I had an usable file in two clicks.
Now with your solution (which is already great, thank you :)), once the lines are differentiated between the header and the data rows, would you have a way for them to line up automatically? Because when I process a full month, I can have 5 to 6000 Excel lines... doing it manually is going to be tough...
It didn't work
I entered in the search area
^("[0-9][0-9][0-9][0-9][0-9]")$
I entered in the replace area
\n\1
which is the code for a line break :)
Then I entered in the search area
^("\d{5}")$
Same, checking the regular expression ... but it finds no occurrences
Moreover, since there are postal codes inside also surrounded by quotes, it really needs to be at the beginning of the line (a bit like the isnum formula associated to the left in Excel)
I tried on Excel but it's too complex if we don't go through VB ... and VB is far from me :)
The ^ means the beginning of a line and the $ the end of a line, so we only take lines with 7 characters "nnnnn"
It might be the line separator of the file, it's cr+lf or lf
We should put a few lines in a response on the forum but better an example file on a deposit site like cjoint.fr
or else remove the $
[moderation]
I don't know if you can open it.