Insert a value before each word

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

Excel

Insert a value before each word using the SUBSTITUTE function

VBA

Insert a value before each word using the VBA Replace function

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

EXCEL

Hard coded formula

Cell reference formula

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

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

GENERIC FORMULA

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

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

GENERIC FORMULA

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

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

EXPLANATION

This formula uses the SUBSTITUTE function to insert a specific value before 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 before 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) before each word in cell B5.

VBA CODE 1. Insert a value before each word

VBA

Hard coded against single cell

Sub Insert_a_value_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Cell reference against single cell

Sub Insert_a_value_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

For x = 5 To 8

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

End Sub

Cell reference against range of cells

Sub Insert_a_value_before_each_word()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub