How To CONCATENATE a Range of Cells + VBA Macro

Bottom line: Learn two different ways to quickly concatenate (join) 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

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

Create Concatenate Formula with Ctrl+Left-click Excel
Create Concatenate Formula for Range of Cells with Concatenate Macro in Excel

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).

Concatenate Individual vs Range of Cells Formula Error Comparison

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.

Create CONCATENATE Formula with Ctrl Left Click in 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.

Create CONCATENATE and AMPERSAND Formulas in Excel GIF

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.
Create CONCATENATE and AMPERSAND Formulas with Separator

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

Concatenate vs Ampersand Formulas in Excel

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.

Add Concatenate Macro Buttons to the Ribbon

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.

Create CONCATENATE Formula with Keyboard Shortcut

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.

98 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,

    Just wanted to thank you for saving me a huge amount of time and tedium through the use of your macro. Absolutely brilliant.

    Best, Trevor

  • There must be a way to do this with a loop. Each line is its own record. The loop does each line separately and runs until the bottom of the current region?

  • can someone share a macro code for my below request.
    Col A has around 100 rows of phrases/words/ sentences.
    Col G1 (named a random column here) should contain data of A1 separated by a pipe i. e “|” then A2 again separated bya pipe | and so on..
    so G1 cell will have A1 | A2| A3|…………….

    Col A
    test
    1test
    check
    QC

  • Can this formula be modified to include INDIRECT(CONCATENATE? Below is a sample of what i use in a table I created. Cell P7 are initials for a staff person but is also the name of their profile worksheet where specific data is stored in a table. In the past, when staff left or was added, i was changing all the formulas so it would match their specific worksheet, now,by just changing the value/initials in column P, all columns with this formula will update to that specific worksheet and extract that data. In example, if P7 = JD (for John Doe), then the INDIRECT(CONCATENATE will search for worksheet ‘JD’ with a lookup range of C14:15. This formula is in cell P2:
    =IF(P7=””,””,LOOKUP(DATA!$B$2,DATA!$G$2:$G$13,INDIRECT(CONCATENATE(“”&$P7&”!”&”$C$4:$C$15″))))

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter