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
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
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.
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
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 & " "