Insert a value after each word

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

Excel

Insert a value after each word using the SUBSTITUTE function

VBA

Insert a value after each word using the VBA Replace function

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

EXCEL

Hard coded formula

Cell reference formula

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

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

GENERIC FORMULA

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

ARGUMENTS
string: A string of words after which you want to insert a value.
value: A value that you want to insert in after each word in a string.

GENERIC FORMULA

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

ARGUMENTS
string: A string of words after which you want to insert a value.
value: A value that you want to insert in after each word in a string.

EXPLANATION

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

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

In this example the formula will insert a word "Exceldome" (hard coded example) or a value in cell C5 (cell reference example) after each word in cell B5.

VBA CODE 1. Insert a value after each word

VBA

Hard coded against single cell

Sub Insert_a_value_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Cell reference against single cell

Sub Insert_a_value_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert characters 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_value_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert characters 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), " ", " Exceldome ") & " Exceldome"
Next

End Sub

Cell reference against range of cells

Sub Insert_a_value_after_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert characters 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("C" & x) & " ") & " " & ws.Range("C" & x)
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
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