Notepad++ – Merge all lines into one and add separator

Here's a way to do it in excel if you paste your lines in column A and change workingRange to your range. Then you can just copy column A back to notepad

Open Excel Developer tab and choose Visual Basic [for applications], or press Alt + F11.

Copy the code below into a new module:

Option Explicit
Public Sub notepadthing()

    Dim workingRange As Range
    'Set your range here
    Set workingRange = Range("A1:A3")
    workingRange.TextToColumns _
    Destination:=workingRange, _
    DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierNone, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False

Dim lastColumn As Long
Dim rowNumber As Long
Dim columnNumber As Long
Dim myString As String


For rowNumber = 1 To workingRange.rows.count
    lastColumn = Cells(rowNumber, Columns.Count).End(xlToLeft).Column
    myString = Cells(rowNumber, 1) & ", "
    For columnNumber = 2 To lastColumn - 1
        myString = myString & Cells(rowNumber, columnNumber) & ","
        If columnNumber Mod 2 <> 0 Then myString = myString & " "
    Next
    Cells(rowNumber, 1) = myString & Cells(rowNumber, lastColumn)
Next

    Application.ScreenUpdating = True
End Sub

Make the spreadsheet you want formatted the current spreadsheet. Switch back to VBA window and click the Run button (looks like a play button on a dvd or music player)

It's just text to columns and concatenation. I'm sure there's easier way to do it.

The way the spacing works is here -

For columnNumber = 2 To lastColumn - 1
   myString = myString & Cells(rowNumber, columnNumber) & ","
   If columnNumber Mod 2 <> 0 Then myString = myString & " "
Next
Cells(rowNumber, 1) = myString & Cells(rowNumber, lastColumn)

It always adds a , but you can decide when it adds a space . Right now column 1 sits alone. So as we go through the columns, odd columns need the space columnnumber Mod 2 <> 0.

To change this, you need to work through the logic: there's a space after column 1 and then I need groups of 4, so column 5, column 9, column 13. But those are difficult numbers to work with and they are just off by 1 to natural multiples of four. So if I subtract one and then check divisible by four..

If (columnNumber - 1) Mod 4 = 0 Then myString = myString & " "

Option 1: Change your replace to use braced numbers:

Edit: Corrected replacement string

\g{1}\t\g{2}\t\g{3}\t\g{4}\t\g{5}\t\g{6}\t\g{7}\t\g{8}\t\g{9}\t\g{10}\t\g{11}\t\g{12}\t\g{13}\t

${1}\t${2}\t${3}\t${4}\t${5}\t${6}\t${7}\t${8}\t${9}\t${10}\t${11}\t${12}\t${13}

Option 2: Your match string to include named groups:

(?<a>.+)\r\n(?<b>.+)\r\n(?<c>.+)\r\n(?<d>.+)\r\n(?<e>.+)\r\n(?<f>.+)\r\n(?<g>.+)\r\n(?<h>.+)\r\n(?<i>.+)\r\n(?<j>.+)\r\n(?<k>.+)\r\n(?<l>.+)\r\n(?<m>.+)

AND, change the replace string to use those groups:

Edit: Corrected replacement string

\g{a}\t\g{b}\t\g{c}\t\g{d}\t\g{e}\t\g{f}\t\g{g}\t\g{h}\t\g{i}\t\g{j}\t\g{k}\t\g{l}\t\g{m}

$+{a}\t$+{b}\t$+{c}\t$+{d}\t$+{e}\t$+{f}\t$+{g}\t$+{h}\t$+{i}\t$+{j}\t$+{k}\t$+{l}\t$+{m}

Your choice, although option 1 is less work, option 2 can come in handy as well.

New Note: The names in option 2, <a> and {a} are not limited to one character. They can be any 'name' type string you want, as long as it is in the normal range of letters, number, and underscores. Not sure if other special characters, including spaces, are valid. Avoiding them is wisest, just in case.