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 FORMULA 1. Insert a comma after each word using the SUBSTITUTE function

EXCEL

Hard coded formula
Insert a comma after each word
Cell reference formula
Insert a comma after each word
=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 TOPICS

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

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel SUBSTITUTE function replaces characters with another in a specified string