microsoft excel 2010 - Counting commas in a text string and adding a new line

I have a huge file (1400 pages, a single string of text) I am trying to make readable in Excel. The file contains comma seperated values, getting this into Excel is normally not a problem, except that the information should be on multiple lines in Excel, and not all on the same line as it is in the document.

What should be one line of information is seperated by 115 commas.

My plan was to use Notepad++ to count 115 commas, make a new line, count 115 commas, make a new line until it has gone through the whole document. After this operation it should be easy to get it structured in Excel. I have however been struggling putting the needed expressions together.

If there is another more sensible way to do this I am all ears, it might be I'm on the wrong path. I've been trying to use the find/replace tool (CTRL+H) in Notepad++.

I have been trying to use "find what":

(,*?)\,{115}

The problem is that this is looking for 115 consecutive commas, but I need it to count the 115 first commas in a line of text, and then make a parapraph/new line and do it again. Any help/pointers would be much appreciated. I've been spending quite some time trying to find the answers.

Answer

• Ctrl+H


• Find what: (?:.+?,){115}\K


• Replace with: \n or \r\n


• check Wrap around


• check Regular expression


• NCHECK . matches newline


• Replace all

Explanation:

(?:         # start non capture group
    .+?     # 1 or more any character, not greedy
    ,       # a comma
){115}      # end group, must appear 115 times
\K          # forget all we have seen until this position

Replacement:

\n          # a line feed, change it to \r\n for windows files

This will keep the comma at the end of each line. If you want to remove it, use:

(?:.+?,){114}.+?\K