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:
- Select the cell where you want the formula to be input and run the macro.
- 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.
- Press OK.
- The macro splits the range into single cell references, so this range reference (A2:C2) is turned into (A2,B2,C2).
- The Concatenate or Ampersand formula is then created in the active cell.
The Concatenate Macro’s Options
- Formula Type – The Concatenate Macro gives you the option of creating a Concatenate or Ampersand formula.
- 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.
- 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.
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.
Concatenate Macro.xlsm (97.3 KB)
Concatenate Macro Other Sheets.xlsm (98.5 KB)
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.
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.
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.