Insert brackets around values in a range

This tutorial shows how to insert brackets around all values captured in a range through the use of a & sign or VBA

EXCEL FORMULA 1. Insert brackets around values in a range using the & sign

EXCEL

Hard coded formula
Insert brackets around values in a range
Cell reference formula
Insert brackets around values in a range
="("&B5&" "&C5&" "&D5&")"
=$C$4&B8&" "&C8&" "&D8&" "&$C$5
GENERIC FORMULA

="("&value1&" "&value2&" "&...&")"

ARGUMENTS
value1: A value that is captured in the first cell in a range.
value2: A value that is captured in the second cell in a range.

GENERIC FORMULA

=open_bracket&value1&" "&value2&" "&...&close_bracket

ARGUMENTS
value1: A value that is captured in the first cell in a range.
value2: A value that is captured in the second cell in a range.
open_bracket: An open bracket sign.
close_bracket: An close bracket sign.

EXPLANATION

This formula uses the the & sign to insert brackets between values in a range of cells.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the bracket signs directly entered in the formula or referenced to a cell that captures the open and close bracket signs.

VBA CODE 1. Insert brackets around values in a range using VBA

VBA

Hard coded against single cell
Sub Insert_brackets_around_values_in_a_range()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert brackets around values in a range of cells
ws.Range("E5") = "(" & ws.Range("B5") & " " & ws.Range("C5") & " " & ws.Range("D5") & ")"

End Sub

Cell reference against single cell
Sub Insert_brackets_around_values_in_a_range()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert brackets around values in a range of cells
ws.Range("E8") = ws.Range("C4") & ws.Range("B8") & " " & ws.Range("C8") & " " & ws.Range("D8") & ws.Range("C5")

End Sub

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

'insert brackets around values in a range of cells

For x = 5 To 6

ws.Range("E" & x) = "(" & ws.Range("B" & x) & " " & ws.Range("C" & x) & " " & ws.Range("D" & x) & ")"
Next x

End Sub

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

'insert brackets around values in a range of cells

For x = 8 To 9

ws.Range("E" & x) = ws.Range("C4") & ws.Range("B" & x) & " " & ws.Range("C" & x) & " " & ws.Range("D" & x) & ws.Range("C5")
Next x

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert a value after each word How to insert characters after each word through the use of an Excel formula or VBA
Insert brackets around value in a cell How to insert brackets around values captured in a cell through the use of an Excel formula or VBA