Add space after nth commas in Excel, Notepad++, or emEditor

csvmacrosmicrosoft excelnotepadvba

Notepad++ 6.8.8
I want to be able to turn several lines that start like this:

0,2,3,45,67,89,01,234,567,890,123,4567,8901
0,7,8,9,0,11,12,13,14,151,162,173,184
x,z,q,r,n,f,t,a,e,f,z,n,a

into

0, 2,3, 45,67, 89,01, 234,567, 890,123, 4567,8901
0, 7,8, 9,0, 11,12, 13,14, 151,162, 173,184
x, z,q, r,n, f,t, a,e, f,z, n,a

A space after the initial 0, comma, (first number with comma afterwards) and then every 2 commas, put one blank space after the comma.

  • Not every line will start with 0 [zero]. Can start with any digit or period . possibly words sometimes.
  • I want each line to be treated independently; maintain their separate line content (one line cannot wrap into another when searching for numbers and commas).

I may have another situation where I will need to add space after comma into groups of 4 after the first comma. example:

x,z,q,r,n,f,t,a,e,f,z,n,a

to

x, z,q,r,n, f,t,a,e, f,z,n,a

Notepad++ is the preferred way to do this (regular expressions, macro, etc), but I do have EmEditor, MS Word / Excel 2010 if this helps. It is difficult to record a macro like this, because the amount of characters before/after each comma can be different. Please also explain the syntax that you use in case adjustments need to be made.

Best Answer

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.

Excel Developer tab VBA screenshot

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)

Excel VBA run module

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 & " "
Related Question