Combine value in cells with a comma

This tutorial shows how to combine values in separate cells with a comma (,) through the use of an Excel formula, with the SUBSTITUTE and TRIM functions or VBA

EXCEL FORMULA 1. Combine value in cells with a comma using the SUBSTITUTE and TRIM functions

EXCEL

Hard coded formula
Combine value in cells with a comma
Cell reference formula
Combine value in cells with a comma
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5)," ", ", ")
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5)," ",F5&" ")
GENERIC FORMULA

=SUBSTITUTE(TRIM(value1&" "&value2&" "&...)," ", ", ")

ARGUMENTS
value1: First value which you want to combine with the second value with a comma between them.
value2: Second value which you want to combine with another value with a comma between them.

GENERIC FORMULA

=SUBSTITUTE(TRIM(value1&" "&value2&" "&...)," ",comma&" ")

ARGUMENTS
value1: First value which you want to combine with the second value with a comma between them.
value2: Second value which you want to combine with another value with a comma between them.
comma: A cell that contains the comma sign (,).

EXPLANATION

This formula uses the SUBSTITUTE and TRIM functions with the & sign to combine values captured in separate cell with a comma.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the comma directly entered in the formula or referenced to a cell that captures the comma sign that is to be inserted between the values that you want to combine.

In this example we are combining the values captured in B5, C5, D5 and E5 with a comma between them.

VBA CODE 1. Combine value in cells with a comma using VBA formula function

VBA

Hard coded against single cell
Sub Combine_value_in_cells_with_comma()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'combine values in separate cells with a comma
ws.Range("F5") = WorksheetFunction.Substitute(WorksheetFunction.Trim(ws.Range("B5") & " " & ws.Range("C5") & " " & ws.Range("D5") & " " & ws.Range("E5")), " ", ", ")

End Sub

Cell reference against single cell
Sub Combine_value_in_cells_with_comma()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'combine values in separate cells with a comma
ws.Range("G5") = WorksheetFunction.Substitute(WorksheetFunction.Trim(ws.Range("B5") & " " & ws.Range("C5") & " " & ws.Range("D5") & " " & ws.Range("E5")), " ", ws.Range("F5") & " ")

End Sub

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

'combine values in separate cells with a comma

For x = 5 To 8

ws.Range("F" & x) = WorksheetFunction.Substitute(WorksheetFunction.Trim(ws.Range("B" & x) & " " & ws.Range("C" & x) & " " & ws.Range("D" & x) & " " & ws.Range("E" & x)), " ", ", ")
Next

End Sub

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

'combine values in separate cells with a comma

For x = 5 To 8

ws.Range("G" & x) = WorksheetFunction.Substitute(WorksheetFunction.Trim(ws.Range("B" & x) & " " & ws.Range("C" & x) & " " & ws.Range("D" & x) & " " & ws.Range("E" & x)), " ", ws.Range("F5") & " ")
Next

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert a comma before first number How to insert a comma (,) before the first number in a cell through the use of an Excel formula or VBA
Insert comma to end of a cell How to add a comma (,) to the end of a cell through the use of an Excel formula or VBA
Insert a comma after first number How to insert a comma (,) after the first number in a cell 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

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
SUBSTITUTE Function TThe Excel SUBSTITUTE function replaces characters with another in a specified string
TRIM Function The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text