Insert a comma after each word

This tutorial shows how to insert a comma after each word through the use of an Excel formula, with the SUBSTITUTE function or VBA

Excel

Insert a comma after each word using the SUBSTITUTE function

VBA

Insert a comma after each word using the VBA Replace function

EXCEL FORMULA 1. Insert a comma after each word using the SUBSTITUTE function

EXCEL

Hard coded formula

Cell reference formula

=SUBSTITUTE(B5," ",", ")&","

=SUBSTITUTE(B5," ",C5&" ")&C5

GENERIC FORMULA

=SUBSTITUTE(string," ",", ")&","

ARGUMENTS
string: A string of words after which you want to insert a comma.

GENERIC FORMULA

=SUBSTITUTE(string," "," "&comma&" ")&" "&comma

ARGUMENTS
string: A string of words after which you want to insert a comma.
comma: A cell that contains the comma sign.

EXPLANATION

This formula uses the SUBSTITUTE function to insert a comma after each word in a string.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the comma sign directly entered in the formula or referenced to a cell that captures the comma sign that is to be inserted after each word in a specific string.

In this example the formula will insert a comma after each word in cell B5.

VBA CODE 1. Insert a comma after each word

VBA

Hard coded against single cell

Sub Insert_a_comma_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert comma after each word
ws.Range("C5") = Replace(ws.Range("B5"), " ", ", ") & ","

End Sub

Cell reference against single cell

Sub Insert_a_comma_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert comma after each word
ws.Range("D5") = Replace(ws.Range("B5"), " ", ws.Range("C5") & " ") & ws.Range("C5")

End Sub

Hard coded against range of cells

Sub Insert_a_comma_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert comma after each word by looping through each cell in the specified range

For x = 5 To 8

ws.Range("C" & x) = Replace(ws.Range("B" & x), " ", ", ") & ","
Next

End Sub

Cell reference against range of cells

Sub Insert_a_comma_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert comma after each word by looping through each cell in the specified range

For x = 5 To 8

ws.Range("D" & x) = Replace(ws.Range("B" & x), " ", ws.Range("C5") & " ") & ws.Range("C5")
Next

End Sub

Related Topic	Description	Related Topic and Description
Insert a value before each word	How to insert characters before each word through the use of an Excel formula or VBA
Insert a value after each word	How to insert characters after each word through the use of an Excel formula or VBA
Count number of characters in a cell	How to count the total number of characters, including spaces, in a cell through the use of an Excel formula or VBA