How To CONCATENATE a Range of Cells + VBA Macro

Bottom line: Learn two different ways to quickly (join) concatenate a range of cells.  This includes the Ctrl+left-click method, and a free VBA Macro that makes it quick & easy to create the concatenate or ampersand formulas.

Skill level: Intermediate

Concatenate: The Good & Bad

The CONCATENATE function can be very useful for combining values of multiple cells into one cell or formula.  One popular use is for creating VLOOKUP formulas based on multiple criteria.

However, you cannot concatenate a range of cells by referencing the range in the CONCATENATE function.  This makes it difficult and time consuming to write the formulas if you have a lot of cells to join together (concatenate).

Option #1: Ctrl+Left-click to Select Multiple Cells

You can hold down the Ctrl key while selecting cells to add to the CONCATENATE formula.  This saves time over having to type a comma after each cell selection.

The following screencast shows how to use the Ctrl+Left-click shortcut.  You do NOT need the macro for this, it is built into Excel.

This is probably the fastest way to add multiple cells to your concatenate formula.  It is a handy shortcut if you are concatenating a few cells, but it can still be time consuming if you are joining a lot of cells together.

Option #2: The Concatenate a Range Macro

Unfortunately there is no simple way to select the entire range you want to concatenate.  So I wrote a macro that makes it possible to concatenate a range.  The following screencast shows the macro in action.

The Concatenate Macro uses an InputBox that allows you to select a range of cells.  It then creates the Concatenate or Ampersand formula by creating an argument for each cell in the selected range.

You can assign the macro to a button in the ribbon or keyboard shortcut.  The macro makes it really fast to create the formulas.

How Does the Macro Work?

The macro basically splits the range reference that is specified with the InputBox, and then creates the formula in the active cell.  Here’s a high-level summary:

1. Select the cell where you want the formula to be input and run the macro.
2. An InputBox appears and prompts you to select the cells you want to concatenate.  You can select a range of cells with the mouse or keyboard.
3. Press OK.
4. The macro splits the range into single cell references, so this range reference (A2:C2) is turned into  (A2,B2,C2).
5. The Concatenate or Ampersand formula is then created in the active cell.

The Concatenate Macro’s Options

1. Formula Type –  The Concatenate Macro gives you the option of creating a Concatenate or Ampersand formula.
2. Separator Character – You can also add a separator character between each cell.  This is handy if you want to add commas, spaces, dashes, or any character between the joined cells.
3. Absolute References – The macro also gives you the option to make the cell references absolute (anchored).  This will add the $ sign in front of the column letter or row number.  This is handy if you are copying the formula in a specific direction and don’t want the relative cell references to change.

CONCATENATE Function or Ampersand Formulas

Cells can also be joined using the Ampersand character (&).  This is an alternative to using the CONCATENATE function.

The following two formulas will produce the same result.

=CONCATENATE(A2,B2,C2)

=A2&B2&C2

The one you use is really a matter of personal preference.  The Concatenate function might have a slight advantage because you can use the Ctrl+Left-click trick to quickly add multiple cells to the formula.

Again, the macro allows you to create either a concatenate or ampersand formula.

The VBA Code

You can download the workbook that contains the code below.

Here is the code for the Concatenate and Ampersand Macros.

Option Explicit

'The following 4 macros are used to call the Concatenate_Formula macro.
'The Concatenate_Formula macro has different options, and these 4 macros
'run the Concatenate_Formula macro with different options.  You will want
'to assign any of these macros to a ribbon button or keyboard shortcut.

Sub Ampersander()
    'Creates a basic Ampersand formula with no options
    Call Concatenate_Formula(False, False)
End Sub

Sub Ampersander_Options()
    'Creates an Ampersand formula and prompts the user for options
    'Options are absolute refs and separator character
    Call Concatenate_Formula(False, True)
End Sub

Sub Concatenate()
    'Creates a basic CONCATENATE formula with no options
    Call Concatenate_Formula(True, False)
End Sub

Sub Concatenate_Options()
    'Creates an CONCATENATE formula and prompts the user for options
    'Options are absolute refs and separator character
    Call Concatenate_Formula(True, True)
End Sub

'------------------------------------------------------------------------
' Procedure : Concatenate_Formula
' Author    : Jon Acampora
' Date      : 10/26/2014
' Purpose   : Create a concatenate or ampersand formula to join cells
'------------------------------------------------------------------------
'
Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)

Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean
Dim sArgSep As String
Dim sSeparator As String
Dim rOutput As Range
Dim vbAnswer As VbMsgBoxResult
Dim lTrim As Long
Dim sTitle As String

    'Set variables
    Set rOutput = ActiveCell
    bCol = False
    bRow = False
    sSeparator = ""
    sTitle = IIf(bConcat, "CONCATENATE", "Ampersand")
    
    'Prompt user to select cells for formula
    On Error Resume Next
    Set rSelected = Application.InputBox(Prompt:= _
                    "Select cells to create formula", _
                    Title:=sTitle & " Creator", Type:=8)
    On Error GoTo 0
    
    'Only run if cells were selected and cancel button was not pressed
    If Not rSelected Is Nothing Then
        
        'Set argument separator for concatenate or ampersand formula
        sArgSep = IIf(bConcat, ",", "&")
        
        'Prompt user for absolute ref and separator options
        If bOptions Then
        
            vbAnswer = MsgBox("Columns Absolute? $A1", vbYesNo)
            bCol = IIf(vbAnswer = vbYes, True, False)
            
            vbAnswer = MsgBox("Rows Absolute? A$1", vbYesNo)
            bRow = IIf(vbAnswer = vbYes, True, False)
                
            sSeparator = Application.InputBox(Prompt:= _
                        "Type separator, leave blank if none.", _
                        Title:=sTitle & " separator", Type:=2)
        
        End If
        
        'Create string of cell references
        For Each c In rSelected.Cells
            sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
            If sSeparator <> "" Then
                sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
            End If
        Next
        
        'Trim extra argument separator and separator characters
        lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
        sArgs = Left(sArgs, Len(sArgs) - lTrim)

        'Create formula
        'Warning - you cannot undo this input
        'If undo is needed you could copy the formula string
        'to the clipboard, then paste into the activecell using Ctrl+V
        If bConcat Then
            rOutput.Formula = "=CONCATENATE(" & sArgs & ")"
        Else
            rOutput.Formula = "=" & sArgs
        End If
        
    End If

End Sub

How To Use The Code

Add Macro Buttons to the Ribbon

In the examples above I have added this code to my Personal Macro workbook.  I then added macro buttons to the ribbon for each of the 4 macros.  I created a new group on the Formulas tab and added the buttons to it.

Once the macros buttons are on the ribbon, you can right-click them and select “Add to Quick Access Toolbar” to add them to the QAT.

Assign Keyboard Shortcut to the Macros

You could also assign a keyboard shortcut to the macro to make this process really fast.  When the InputBox is opened, the focus is set back on the worksheet which means you can select the input range with the keyboard.  In the following screencast I create concatenate formula only using the keyboard.

I'm triggering the macro by placing the macro button in the Quick Access Toolbar, and then pressing the Alt+Button Position shortcut for the QAT.  Checkout this article on how to use the QAT Keyboard Shortcuts for more details.

Additional Resources

Concatenate with Line Breaks – Dave Bruns over at ExcelJet has a great article and video on how to add line breaks to your concatenate formula.  Great tip for joining mailing addresses.

Combine Cells Without Concatenate – Debra Dalgleish at Contextures explains how to create the ampersand formulas.

Concatenate Multiple Cells using Transpose – Chandoo has an interesting approach to this problem using the TRANSPOSE Function.

Conclusion

This tool should make it a lot faster and easier to create Concatenate or Ampersand formulas.  It might not be something you use everyday, but it's a great one to have in your toolbox.

Please leave a comment below with any questions.