How To Concatenate a Range of Cells in Excel + VBA Macro
80

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

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.

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.

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 80 comments
sazzad - April 3, 2017

how to get 1 = yes in same cell by
example:- I will press button “1” it will automatically become “Yes”. I will press “2” it will automatically become “No”. I will press “3” it will automatically become “No Comment”…Please help me Sir

Thanks

Reply
sazzad - April 3, 2017

after press 1 how it will be automatically yes ???

Reply
Richard - January 8, 2017

Hi Jon,

Thanks for this great macro function, it looks like it will speed up things a lot for us in our business.

One function we’d like to add is the ability to select a range of cells, and CONCATENATE into one cell using two different separators. One separator between column items, then another separator between each new row. So the items will be separated by a : and then it adds a separator , before adding the next row into the same cell.
i.e
J3,K3,L3,M3
J4,K4,L4,M4

So the above would concatenate into J3:K3:L3:M3,J4:K4:L4:M4 inside a single cell.

So with your macro when it asks which separator to use, you would select one type for column items.
Then it would ask you to choose the separator between new rows.

Appreciate your feedback.

Regards

Reply
Ajit Singh - December 7, 2016

Hi,

thanks for the useful macro, it really work for me. However when i tried it for more than 200 cells it gives error msg 7. Could you please help to guide how i can use this macro for more than 800 rows.

I have to copy 800 rows data in one row.

Reply
    Jon Acampora - December 10, 2016

    Hi Ajit,

    That is going to be a limitation of the CONCATENATE function. If you are using Excel 2016, there is a new TEXTJOIN function that allows you to input a range.

    Otherwise you will probably need to use a macro that does the join in the code and returns the value to the cell instead of the formula.

    I hope that helps.

    Reply
      Ajit Singh - February 23, 2017

      Hi Jon,

      thanks for the valuable answer. Yes i am using the 2016 excel and I tried to create macro for TextJoin function, however i am not in good macro. Could you please help to share the macro coding for textjoining function.

      Reply
Laban - December 3, 2016

I need a macro to concatenate text in the selected adjacent cells and return the text in the active cell in the selection.

Reply
Adam Derham - November 23, 2016

Jon, I need to concatenate cells based on the value of another cell. I need my spread sheet to look at the value of the cells in RC1 and for every row that has the same value in RC1 concatenate the value found in column 4 into one single cell. Any idea on how to use this macro for that type of function?

Reply
    Jon Acampora - December 10, 2016

    Hi Adam,
    I believe that can be accomplished with an array formula. I’m not exactly sure what the formula would be though. You might want to try posting in one of the Excel forums. Thanks!

    Reply
Praveen Kumar M - November 3, 2016

Thanks sir..You made my life simpler and help me to achieve

Reply
Chris - October 27, 2016

Hi Jon thanks for the terrific Macro. I was hoping you could help me out, how can I updated the macro on loop for multiple ranges based off a table. For example it would auto populate the cell formula for Concatenate_Options based on the table below with a separator “, “?

RANGE OF CELL A
Low High RANGE
3 99 $A$3:$A$99
100 199 $A$100:$A$199
200 299 $A$200:$A$299
300 399 $A$300:$A$399

Reply
jheng - October 6, 2016

Hi Jon,

How will i make a macro with this output.
i have a columns with series of number like this
COLUMN A
A2: 9325A
A3: 9326A
A4: 9327A
A5: 9328A
A6: 9330B
A7: 9333C
I want my out put cell to be like this
COLUMN B
B2: 9325A THRU 9328A, 9330B, 9333C (and this cell has a limit characters)

Reply
    Jon Acampora - October 11, 2016

    Hi Jheng,
    Great question! You would probably need to loop through each cell and test if the value is 1 greater than the previous value. Once the next value is not 1 greater than the previous, then stop and create the Thru string.

    I will put this on my list of future posts for macros. Thanks!

    Reply
      jheng - October 24, 2016

      Hi Jon,

      Thanks for the Help. But can you give me a sample coding for Looping Functions in macros, since I am only a newbie when it comes to macro. Thanks again in advance. Godspeed

      Reply
Elijah - August 26, 2016

This is great, but why go to the trouble of writing all that code to write a preexisting formula, when you can just make a UDF (User Defined Formula) called ConcatenateRange:

Public Function ConcatenateRange(rRange As Range, Optional sDelimiter As String) As String
‘concatenates the values in the given range of cells. inserts a the given delimiter between them
Dim c As Variant

For Each c In rRange.Cells
If Not c.value = “” Then
If ConcatenateRange = “” Then
ConcatenateRange = c.value
Else
ConcatenateRange = ConcatenateRange & sDelimiter & c.value
End If
End If
Next c

End Function

Reply
    Jon Acampora - August 30, 2016

    Hi Elijah,
    Thanks for posting the code. The major limitation with UDFs is that they require macro enabled workbooks. Not everyone can use macro enabled files, or they don’t want users to have to enable macros. This macro allows you to create the CONCATENATE formulas in a workbook that does not contain macros. I hope that helps answer your question.

    Reply
JM - July 13, 2016

Hi Jon!

Thank you very much for this. It really helped me a lot on my reports. However, there are reports that I need to concatenate 6,000+ data. What is the best way to approach that problem?

Thank you in advance!

JM

Reply
    Jon Acampora - July 18, 2016

    Hi JM,
    That sounds like a lot of data to concatenate. You can use/write a macro for that, although you might want to examine your process further and see if you really need to concatenate that many cells. There might be a better solution to get the end result. I hope that helps.

    Reply
Denis Mahmic - June 15, 2016

Hi, I hope you can help me here as I am new in VBA.

I have two columns A and B. I wanted to select cells in column B that have value 0 in column A. For example if A1 = 0 Select B1. Also, columns are changing the size so it would be nice to have something in general that will work even if cells are empty. I have managed to create one step if I manualy select cells for example (B9:B11) but I am not sure how to automate it.

Sub Copy()

Range(“B9:B11”).Select
Selection.Copy
Range(“B6”).Select
Selection.End(xlDown).Select
Range(“B12”).Select
ActiveSheet.Paste
Sheets(“Account receivables”).Select
Range(“D6”).Select
Selection.End(xlDown).Select
Range(“D14”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Incoming PYMT Cycle 2”).Select
Range(“A3”).Select
Selection.End(xlDown).Select
Range(“A9”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Aztec Manager”).Select
Range(“A4”).Select
Selection.End(xlDown).Select
Range(“A10”).Select
ActiveSheet.Paste
End Sub

Regards,
Denis

Reply
    nitinkumar - June 29, 2016

    how to use this condition in excel sheet with example.

    Reply
Scott - May 20, 2016

This is Excellent! I often have 10 to 20 cells that need to be joined with commas between them. Thanks to Excel Campus people on my team think I am a genius.

Reply
Walter Day - May 17, 2016

Hi Jon,

This is a great tool for keeping up with assets that are rented to customers, as I periodically have to check on older orders to make sure items have returned. I’m running into a problem though, when I click Cancel I get Error 424: Object required (highlighting the “Set rSelected” section). I haven’t modified your code at all (other than to test the error), so I thought the “On Error Resume Next” would catch that but it didn’t. I tried taking that out and adding (below “set rSelected”) “If rSelected = “” then Exit Sub”. I tried adding an “On Error GoTo cleanup” which also failed… Can you tell me why it keeps failing on clicking the Cancel button? I’m using Excel 2010.

Reply
    Jon Acampora - May 17, 2016

    Hi Walter,
    Great question! Do you have the following line in your macro?

    If Not rSelected Is Nothing Then

    This basically checks to see if the cancel button was pressed when the range is set above. Let me know if that helps. Thanks!

    Reply
      Walter Day - May 17, 2016

      Yes sir, I noticed that in the code too and thought it strange that with all of these steps in place it would still throw an error. Apparently it’s actually creating an error when trying to set rSelected, and not later in the sub?

      Reply
        Jon Acampora - May 17, 2016

        Oh, do you have Application in front of InputBox?

        Application.InputBox

        Reply
          Walter Day - May 17, 2016

          Yes sir, your code was copied directly from this webpage. The line highlighted on the error is:

          Set rSelected = Application.InputBox(Prompt:= _
          “Select cells to create formula”, _
          Title:=sTitle & ” Creator”, Type:=8)

          Reply
Daniel Olu - May 13, 2016

Hi Jon,

Hope you are well this macro worked for me but I’m just wondering if you can help with another issue, I’ll try detail is as best as possible.

Scenario:

I have a range of data pulled from a software but when it exports into Excel the formatting is not very user friendly…

What’s needed:

I want to Concatenate 4 cells and move them to a another cell below the data as it provides more information on it.

Example (manual steps):

Cell B2: Contains data name
Cells B3 to E3: Contains the entities I want to concatenate
Cell : F3 Contains the oncatenated entities

(1. I then copy and repaste these ‘as values’)
(2. Then I delete the cells in B3 to E3)
(3. I copy and paste F3 into B3)

Macro trouble!:

I record these steps as a Macro but as this needs to be done for a lot of data I can’t seem to get the formulae (or select the correct ranges) that will allow me to sort this via the macro automatically for c. 900 entries.

If you could help with this it would be much appreciated!

Thanks,
Dan

Reply
Ruben - May 13, 2016

Hi Jon!
Very nice tool!

I´d like to ask you something: how could be modified the code so that when defining the range to be concatenated only visible cells (under an active filter) will be concatenated?

Unfortunately I´m not familiar with VBA programming 🙁
Today I just made my first macro making use of this …

Sub CopyOnlyVisibleCells()

Dim Rng As Range
Set Rng = Selection.SpecialCells(xlCellTypeVisible)
Rng.Copy

End Sub

…and I wanted to modify your code to be able to concatenate only visible cells, but unfortunately I lack of knowledge for that…

Greetings from Germany!,
Ruben

Reply
    Jon Acampora - May 17, 2016

    Hi Ruben,

    Great question. In the macro you can modify the following line to include the visible cells only with the SpecialCells method.

     For Each c In rSelected.SpecialCells(xlCellTypeVisible).Cells

    I also have a free video series on macros & VBA you might be interested in. I hope that helps. 🙂

    Reply
      Ruben - May 17, 2016

      Hi Jon,
      Perfect! now it works as I needed it (with filtered ranges).
      Thanks also for the link to the video series!!

      Have a nice day! 🙂

      Reply
mark - April 30, 2016

How do i get results show the following values:
A B C
10 10 =CONCATENATE(A1,”,”,B1,”,”,C1) RESULTS 10,,10
10 20 30 =CONCATENATE(A1,”,”,B1,”,”,C1) RESULTS 10,20,10
10 30 =CONCATENATE(A1,”,”,B1,”,”,C1) RESULTS 10,30,

ABOVE IT IS SHOWING COMMA IN BETWEEN TWO VALUES OR AFTER. I DONT WANT THE COMMA WHERE THE VALUE IS NOT THERE

Reply
    Jon Acampora - May 9, 2016

    Hi Mark,
    If you are using Excel 2016 then there are some new functions that will help with this. TEXTJOIN is one of the new functions it has an argument that allows you to skip the blank cells so you won’t get those extra commas.

    If you are not using Excel 2016 then we could add some code to the macro/function above to skip blanks.

    For Each c In rSelected.Cells
       If c.value <> ""
          sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
          If sSeparator <> "" Then
             sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
          End If
       End If
    Next

    Add this line will check if the value of the cell is NOT blank: If c.value <> “” then

    We could also add an argument to the function for skipping blanks (bSkip), then use it in the If statement to test if it is set to TRUE or FALSE.

    If bSkip and c.value <> "" then

    I hope that helps. Let me know if you have any questions. Thanks!

    Reply
Paul - April 20, 2016

Please scratch m last comment. I figured out the issue there. Is there a way to apply this macro to many rows? It looks like I need to execute each row. I tried selecting all cells that needed filled, but that did not work.

Reply
    Jon Acampora - April 26, 2016

    Oh, got it. Yeah, it will create the one formula, then you can copy it down to multiple rows. That is a good suggestion for a feature enhancement though… 🙂

    Reply
Paul - April 20, 2016

Getting an error “Cannot execute code in break mode” when trying:
‘Create string of cell references
For Each c In rSelected.Cells
If c “” Then
sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
If sSeparator “” Then
sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
End If
End If
Next

Any ideas?

Reply
    Jon Acampora - April 26, 2016

    Hi Paul,

    Are you running another macro? Try hitting the stop button in the VB Editor first. Let me know if that helps. Thanks!

    Reply
Melissa - April 19, 2016

Hi Jon,

I’m learning the ends and outs of how to use the concatenate function while on the job. I’m trying to create a string of following rows (J3-J83), problem I don’t want to add each individual row (=concatenate(J3,J4, etc..). How would I create a string using concatenate function for this situation?

Reply
Praveen Anku - April 10, 2016

Hi Jon,

I wanted one help in merging the data from different column and different sheets into one, is it possible by macro, if yes can you help me with the code.

here is how my data looks like, i wanted to copy 5 cells data from 50 different sheets,in sheet 1 data in this order column A,B,C,D,E, however in sheet 2,3, 4 & 5 it is A,D,E,B,C and also rest of the sheet it is A,C,B,D,E in these order at finally i want to compile these data in A,B,C,D,E order in one sheet, please help in im merging the data for this,

Let me know if you need further details about this. i can send the excel to you.

Thank you in advance,
Praveen Ankolekar

Reply
Andrew - April 4, 2016

Just came across this.

Great code, but was a little OTT for what I needed, which is essentially a basic concatenation for a single row.

I created a tiny custom function to achieve this and sharing in case anyone else could benefit.

Simply:

Function CONCATLONG(a As range) As String

Dim i As String

For Each c In a
i = i & c.Value
Next c

CONCATLONG = i

End Function

That’s it. In your result cell, just type CONCATLONG(A2:A54), or whatever 1 dimentional range you like, and it’ll spit out everything concatenated.

Reply
Jeffrey Adik - January 7, 2016

Jon, on further review it seems the error is caused by a variable or other limitation which crashes the macro should the intended array exceed 128 items.

Any thoughts on how to resolve?

Reply
    Jon Acampora - January 22, 2016

    Hi Jeffrey,
    Great point. The limit on CONCATENATE is 255 items or 8,192 characters. You are probably hitting the character limit.

    There are two ways around it. You could use the Ampersand macro option. That joins the values with an ampersand character instead of using the CONCATENATE function.

    You can also add code to the macro to check if there are more than 255 cells selected or 8,192 characters in the selection. Let me know if you are interested in that code.

    Thanks!

    Reply
Jeffrey Adik - January 7, 2016

Jon,
Many thanks for your time and effort creating these macros. They look great & I believe they will serve my purposes… *however* when I run the macro I’m receiving an error:

Run-time error ‘1004’:
Application-defined or object-defined error

Upon searching for solutions, it seems to be a sheet referencing problem, but it isn’t clear how to resolve.

Reply
Tim B - November 6, 2015

‘————————————————————————
‘ Procedure : Concatenate Text
‘ Author : Tim Bennett
‘ Date : 11/6/2015
‘ Purpose : Concatenate selected text into first column
‘————————————————————————

‘Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)
Sub Concatenate()

Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean

‘Set variables
Set rOutput = ActiveCell
bCol = False
bRow = False

On Error Resume Next

‘Use current selection
Set rSelected = Selection

On Error GoTo 0

‘Only run if cells were selected and cancel button was not pressed
If Not rSelected Is Nothing Then
sArgs = “” ‘Create string of cell values
firstcell = “”

For Each c In rSelected.Cells
If firstcell = “” Then firstcell = c.Address(bRow, bCol)
sArgs = sArgs + c.Text + ” ” ‘build string from cell text values

c.Value = “” ‘ Clear out the cells taken from
Next

‘Put the result in the first cell
Range(firstcell).Value = sArgs

End If
End Sub

Reply
Arthur - October 27, 2015

Hi guys,

If you want something trully effective, try this !
It replaces the concatenate function and enables to concatenate a range containing multiple cells. It comes in quite handy to concatenate string dynamically. Tell me what you think about it !

Function SuperConcat(ByRef rng As Range) As String

SuperConcat = “”
For Each cell In rng
If cell.Value “” Then
SuperConcat = Trim(SuperConcat + ” ” + cell.Value)
End If
Next cell
End Function

Reply
    Melissa - February 17, 2016

    Hi Arthur,

    I’m learning the ends and outs of how to use the concatenate function while on the job. I’m trying to create a string of following rows (J3-J83), problem I don’t want to add each individual row (=concatenate(J3,J4, etc..). How would I create a string using concatenate function for this situation?

    Reply
AJ - October 13, 2015

Thank you for this!

Reply
Grey - October 1, 2015

Hello,
Perhaps you would be so kind as to assist me with what I am trying to do. You’re example is almost spot on to what I am doing, however, I am working with a list of names that changes based on a user input. I am grouping people with their current task, so the user selects the task, a macro sorts on that task, delete out all hidden lines through a loop and leaves each persons name in columns B C and D. The intent is to write a loop that will go through each row and concatenate the three columns into a string. However, I’m not certain how to write it so that it offsets each time. Example, you select a task that has 10 people working on it, I need it to create a clean form from raw data.

Do Until IsEmpty(ActiveCell)
Sheets(“Data”).Select
Dim Name As String
Name = Range(“B2”) & “, ” & Range(“C2″) & ” ” & Range(“D2”)
Sheets(“Form”).Select
Range(“A3”).Select
ActiveCell.Value = Name
Loop

The problem is, after the first loop, I need Ranges B3, C3, D3 and A4.
Can you use a variable as a range, and offset it each time it goes through the loop? Also, will doing this set the cell on the FORM sheet to be a formula that will, once this works, update each time I change the value of the string from one persons name to another?

Reply
    Jon Acampora - October 7, 2015

    Hi Grey,
    You can use the Offset property for that and feed it a variable that increments ever time you get to the end of the loop.
    Here is the reference with the offset property.

    Range(“B2”).Offset(lRow)

    Insert the following line above the Loop line.

    lRow = lRow + 1

    This will add 1 to lRow for every iteration in the loop and allow your macro to continue down the page. I hope that helps.

    I also have a free video series on macros & VBA that you might be interested in.

    https://www.excelcampus.com/vba-training-my-first-macro/

    Thanks!

    Reply
Chris Sandi - August 22, 2015

Very nice but does not concantenate from other sheets in the workbook.

Reply
    Jon Acampora - August 22, 2015

    Hi Chris,
    I added a file above that contains the code for it to work with other sheets. I had a few other requests for this and had forgotten to upload the file. Thanks for the suggestion!

    Reply
Madhav - June 30, 2015

Extremely useful macros!
Kudos!
Thanks for creating and double thanks for sharing them..

Reply
Mihkel Mikishev - June 19, 2015

Thank you!

You saved me from a lot of work.

13668 Unique values divided to 256 groups. I organized my groups in little under 10 minutes.

Reply
lorris - February 24, 2015

Just to better explain,

if rows 5,6,7,8,9 are merged in column A, is there a way to have the corresponding rows in D in a concatenate 🙂

your macro is already very helpful, thanks for the help!!

Lorris

Reply
    Jon Acampora - February 24, 2015

    Hi Iorris,
    Yes, this post is still active 🙂 Thanks for the comment! I’m not sure I fully understand your scenario. Would you mind sending me an example file? You can send it to jon@excelcampus.com. I will be happy to take a look at it. Thanks!

    Reply
      lorris - February 26, 2015

      Hi Jon,

      I sent the email, let me know if you need more explanations.

      Thanks a lot for your help 🙂

      Reply
lorris - February 24, 2015

is this post still active?

I have an issue with several tables where I need to combine answers.
to describe the table:
4 columns
A= question number
B= doesn’t matter
C=doesn’t matter
D=Answer
the format of each table is different (some rows have been inserted to have a clean aspect when reading the table)
however, the corresponding question number will merge all the rows in which the answer is

in other words:
question 1:
A2:A7 are merged and number “1” is inside
D2:D7 one information in each row

I was trying to use your macro to concatenate the answers, but I have several thousands of answers.

would you know a way by which this could be solved?
thanks a lot

Reply
Jason - January 19, 2015

Hi Jon, thanks for sharing this macro! One issue I ran into…when I try to run this macro on over 255 records I get a run-time error and when I click debug it takes me to this line: rOutput.Formula = “=CONCATENATE(” & sArgs & “)”

Could you explain what’s happening and the best workaround?

Reply
    Jon Acampora - January 20, 2015

    Hi Jason, Great question! Basically you have hit the 255 string limit of the function. Here is the documentation on the CONCATENATE function that explains the limitation.

    One workaround is to make sub joins in a few cells, then join (concatenate) those cells together. For example, let’s say you have 600 cells to join. Join the first 200 in one cell using the CONCATENATE macro. Then join the next 200 cells in another cell. And the last 200 in another cell. Then join those three cells together. In essence you are putting the joins in smaller batches/groups, then joining the groups.

    Let me know if you have any questions. Thanks!

    Reply
Craig - December 31, 2014

I did not want to concatenate when cells in the range are empty:

'Create string of cell references
For Each c In rSelected.Cells
If c <> "" Then
sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
If sSeparator "" Then
sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
End If
End If
Next

Reply
    Jon Acampora - December 31, 2014

    That’s a great modification!

    For those wondering what the change is, Craig added the following line:

    If c <> "" Then

    This checks to see if the cell (c) is not blank. If it is not blank then it runs the lines of code to add the cell address to the argument string (sArgs).

    Thanks for sharing Craig!

    Reply
      Philip - April 20, 2015

      Hi Jon,

      I love this tool! It’s helping me a lot. Unfortunately I am not that familiar with VBA yet and have a question regarding:

      If c “” Then

      I made the adjustments within the macro from Craigs post, but when I run it an error occurs in:

      sArgs = Left(sArgs, Len(sArgs) – lTrim)

      Is there a chance you could explain what I’d need to change in the code?

      I have a excel sheet with more than 4000 rows and 250 columns of which I need to concatenate a lot of cells with “;”, but half of them are empty.

      Thanks for sharing the code with us. I really appreciate it!

      Best,

      Phil

      Reply
        Jon Acampora - April 23, 2015

        Hi Philip,

        Are all the cells you selected blank? If so, that error will occur because the length of sArgs is zero and the Left function will fail when you pass a negative number as an argument.

        You could put another IF statement before that line to test the length of sArgs. If it is completely blank then you don’t want to create a formula at all.


        If Len(sArgs) >0 Then
        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

        Let me know if you have any questions. Thanks!

        Reply
MF - October 31, 2014

Cool. What a handy tool!

I used to to it with in a manual way by using Find and Replace trick.
I would input the formula =A1:A10 for example, then F2, F9, remove “=”, using Find and Replace for the delimiter that I want. Though a manual one, it works pretty fast, to me. 😛

Cheers,

Reply
    Jon Acampora - November 1, 2014

    Thanks MF! I’m not sure I understand your Find and Replace technique. Did you use this to concatenate? How did you convert A1:A10 to list each cell in that range?

    Reply
      MF - November 1, 2014

      Hi Jon,
      Sorry for not saying that clearly… 😛

      Suppose I have a to k in A1:10 respectively: After I input =A1:A10,
      F9 would give me the following in the formula bar:
      ={“a”;”b”;”c”;”d”;”e”;”f”;”g”;”h”;”i”;”k”}

      Then I manually delete =, { , }
      Follow by using Find and Replace
      Replace ” with nothing
      Replace ; with the delimiter (say , space) I need.

      I’ve got “a, b, c, d, e, f, g, h, i, k” in just 10 sec.

      Yes. It’s manual; and the result is hard-coded; and of course, it is much slower than using codes. But that’s the way I did without much VBA skills. 🙂

      Reply
      veste adidas marron - March 29, 2017

      San Francisco will head to Seattle next Sunday for the right to play in Super Bowl XLVIII.

      Reply
Dave Bruns - October 31, 2014

Very nice, John! It’s always seemed a little weird to be that CONCATENATE doesn’t know how to handle a range.

I love the control + click trick for all kinds of multiple argument functions (SUM, etc).

Kinda related – I made a short video a while back that showed how to use name ranges to make concatenation a little easier when you need to add line breaks, spaces, and the like: https://exceljet.net/tips/clever-concatenation-with-line-breaks

I don’t actually do this very often, but when you need to concatenate a lot complicated stuff, it can be handy.

Reply
    Jon Acampora - November 1, 2014

    Thanks Dave! I actually just added a link to your page yesterday. I forgot to add this when I originally published the post. I like your technique and the macro could be updated to handle a cell reference or named range as a separator, instead of text only. If anyone wants to take on the challenge, please feel free to post the code. 🙂

    Thanks again Dave!

    Reply
Jeff Weir - October 30, 2014

Good idea, Jon. Probably handier than a UDF, because the file remains macro free.

Reply
    Jon Acampora - October 31, 2014

    Thanks Jeff! Yes, I rarely use UDFs because they have to travel with the workbook, or be in an add-in that every user has installed.

    Reply
Chris Macro - October 28, 2014

That was one thing that always ticked me off about Concatenate. I ended up making a much simpler version of what you created with VBA a while back to solve this very issue. Love the versatility in your code though! Great job Jon 🙂

Reply
    Jon Acampora - October 28, 2014

    Thanks Chris! I tend to use ampersand formulas more than concatenate because I was always frustrated with it too, and that lead me to add the option for the ampersand formulas.

    The macro was originally two separate macros, one for concatenate and one for ampersand. I then worked on combining them and simplifying the code as much as possible. It could probably still be reduced a bit, but it works.

    I always like to learn different ways to code. I think that’s one of the fun parts of coding. So if anyone has a suggestion on how to do something differently in the code, please leave a comment.

    Reply
Peter Raiff - October 28, 2014

Great macros! These will save me a bunch of time. The option versions are genius!

Reply

Leave a Reply: