Excel – Add space between numbers in Excel

I'm not sure what you mean by "keeping the data in one cell".

A formula requires putting the formula in a different cell. For example:

=TRIM(REPLACE(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),0," "))

will place a space before the first digit. The TRIM wrapper is in case there already is a space.

If you need to have it happen within the cell, you will need a VBA Macro as a formula cannot alter the contents of a different cell.

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