How to Add a Space After Every Comma in Excel Using VBA

In today's post, I'm going to show you how to add a space after every comma in cells in Excel using VBA. This can be particularly useful when formatting text data to improve readability. For instance, if you have a list of phrases separated by commas without spaces, you can use this simple VBA code to add a space after each comma.

Let’s consider this example worksheet.

Here's the complete code:

Sub AddSpaceAfterComma()

Dim MyCell As Range
Dim SpaceRemovedString As String
Dim CorrectedString As String

For Each MyCell In ActiveSheet.UsedRange

    If MyCell.Value <> "" Then
            
        MyPhrase = MyCell.Value
               
        CorrectedString = Replace(MyPhrase, ",", ", ")
        
        'put corrected sentence in relevant cell
        MyCell.Value = CorrectedString
        
    End If
    
Next

MsgBox "Completed!", vbInformation, ""

End Sub

Let's go through the code step-by-step.

First, the code starts by looping through each cell in the used range of the active sheet:

For Each MyCell In ActiveSheet.UsedRange

Inside the loop, it checks if the cell is not empty:

If MyCell.Value <> "" Then

If the cell is not empty, it stores the value of the cell in a variable called MyPhrase:

MyPhrase = MyCell.Value

Next, it uses the Replace function to find every comma in the phrase and replace it with a comma followed by a space:

CorrectedString = Replace(MyPhrase, ",", ", ")

After correcting the string, it puts the corrected string back into the cell:

MyCell.Value = CorrectedString

Finally, after the loop is done, it displays a message box to indicate that the process is complete:

MsgBox "Completed!", vbInformation, ""

By following these steps, you can easily add a space after every comma in each cell in your used range, making your text data cleaner and more readable. This is the result you will get when you run the above macro.

Improved Version to Handle Existing Spaces

Sometimes, some phrases may already have spaces after the commas. If this happens, the result will have two spaces after the comma.

To avoid this, we can first remove all spaces after the commas and then add a single space again. This ensures consistent formatting without double spaces.

Here's the improved version of the code:

Sub AddSpaceAfterComma_V2()

Dim MyCell As Range
Dim SpaceRemovedString As String
Dim CorrectedString As String

For Each MyCell In ActiveSheet.UsedRange

    If MyCell.Value <> "" Then
            
        MyPhrase = MyCell.Value
        
        'First remove all the spaces after comma
        MyPhrase = Replace(MyPhrase, ", ", ",")
               
        CorrectedString = Replace(MyPhrase, ",", ", ")
        
        'put corrected sentence in relevant cell
        MyCell.Value = CorrectedString
        
    End If
    
Next

MsgBox "Completed!", vbInformation, ""

End Sub

In this improved version, the key change is the addition of a step to first remove any spaces after commas. This is done by:

MyPhrase = Replace(MyPhrase, ", ", ",")

This step ensures that any existing spaces after commas are removed, preventing the creation of double spaces when we add the new space. After this step, the rest of the process is the same as in the first version, where we add a space after each comma and put the corrected string back into the cell. This ensures that there is exactly one space after each comma, regardless of the original formatting.