Insert a comma after first word

This tutorial shows how to insert a comma after the first word in a string through the use of the REPLACE, FIND and SUBSTITUTE functions, or VBA

EXCEL FORMULA 1. Insert a comma after first word using the REPLACE and FIND functions

EXCEL

Hard coded formula
Insert a comma after first word
Cell reference formula
Insert a comma after first word
=REPLACE(B5,(FIND(" ",B5)),0,",")
=REPLACE(B5,(FIND(" ",B5)),0,C5)
GENERIC FORMULA

=REPLACE(string,(FIND(" ",string)),0,",")

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

GENERIC FORMULA

=REPLACE(string,(FIND(" ",string)),0,comma_ref)

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

EXPLANATION

This formula uses the REPLACE and FIND functions to insert a comma after the first word in a string. The FIND function is used to find the first occurrence of a space and the REPLACE function is used to replace the space with a comma and a space (, ).
Click on either the Hard Coded or Cell Reference button to view the formula that has the comma sign to be inserted directly entered into the formula or referenced to a specific cell that captures the comma sign.

In this example the formula will insert a comma after the first word in the selected cell, which is Exceldome.

EXCEL FORMULA 2. Insert a comma after first word using the SUBSTITUTE function

EXCEL

Hard coded formula
Insert a comma after first word
Cell reference formula
Insert a comma after first word
=SUBSTITUTE(B5," ",", ",1)
=SUBSTITUTE(B5," ",C5&" ",1)
GENERIC FORMULA

=SUBSTITUTE(string," ",", ",1)

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

GENERIC FORMULA

=SUBSTITUTE(string," ",", ",comma_ref)

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

EXPLANATION

This formula uses the SUBSTITUTE function to insert a comma after the first word in a string.
With this formula you can enter the values, that will be returned if the cell is empty or not, directly into the formula or reference them to specific cells that capture these values.
Click on either the Hard Coded or Cell Reference button to view the formula that has the comma sign to be inserted directly entered into the formula or referenced to a specific cell that captures the comma sign.

In this example the formula will insert a comma after the first word in the selected cell, which is Exceldome.

VBA CODE 1. Insert a comma after first word using the REPLACE and FIND functions

VBA

Hard coded against single cell
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a comma after first word in a string
ws.Range("C5").Formula = "=Replace(B5,(Find("" "",B5)),0,"","")"

End Sub

Cell reference against single cell
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a comma after first word in a string
ws.Range("D5").Formula = "=Replace(B5,(Find("" "",B5)),0,C5)"

End Sub

Hard coded against range of cells
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a comma after first word in a string
For x = 5 To 8

ws.Range("C" & x).Formula = "=Replace(B" & x & ",(Find("" "",B" & x & ")),0,"","")"

Next x

End Sub

Cell reference against range of cells
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a comma after first word in a string
For x = 5 To 8

ws.Range("D" & x).Formula = "=Replace(B" & x & ",(Find("" "",B" & x & ")),0,C5)"

Next x

End Sub

VBA CODE 2. Insert a comma after first word using the SUBSTITUTE function

VBA

Hard coded against single cell
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a comma after first word in a string
ws.Range("C5").Formula = "=Substitute(B5,"" "","", "",1)"

End Sub

Cell reference against single cell
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert a comma after first word in a string
ws.Range("D5").Formula = "=Substitute(B5,"" "",C5&"" "",1)"

End Sub

Hard coded against range of cells
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a comma after first word in a string
For x = 5 To 8

ws.Range("C" & x).Formula = "=Substitute(B" & x & ","" "","", "",1)"

Next x

End Sub

Cell reference against range of cells
Sub Insert_a_comma_after_first_word()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert a comma after first word in a string
For x = 5 To 8

ws.Range("D" & x).Formula = "=Substitute(B" & x & ","" "",C5&"" "",1)"

Next x

End Sub

RELATED TOPICS

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 comma before each word How to insert a comma before 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

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
FIND Function The Excel FIND function returns the position of a specific sub-string within a string
SUBSTITUTE Function The Excel SUBSTITUTE function replaces characters with another in a specified string