Insert a comma before each word

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

Excel

Insert a comma before each word using the SUBSTITUTE function

VBA

Insert a comma before each word using the VBA Replace function

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

EXCEL

Hard coded formula

Cell reference formula

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

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

GENERIC FORMULA

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

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

GENERIC FORMULA

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

ARGUMENTS
string: A string of words before 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 before 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 before each word in a specific string.

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

VBA CODE 1. Insert a comma before each word

VBA

Hard coded against single cell

Sub Insert_a_comma_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Cell reference against single cell

Sub Insert_a_comma_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Hard coded against range of cells

Sub Insert_a_comma_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert comma in front of 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_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

For x = 5 To 8

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

End Sub

Related Topic	Description	Related Topic and Description
Insert a comma after each word	How to insert a comma after each word through the use of an Excel formula or VBA
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