Batch string concatenation in Excel

Question

I have a couple hundred of cells in Excel I would like to concatenate into a single string. Is there a simpler method of doing this than going through them one by one manually in order to type them into CONCATENATE(A1, A2, ....)?

CONCATENATE(A1:A255) does not work.

Answer 1

Just add your deliminator in one concatenation:

=concatenate(A1, ",")

Then copy all the concatenations, paste them as Values. Then Copy those Values, paste them in a transposition. Then copy the Transposed values and paste them into a word editor. Do a find for the deliminator AND the space preceding the values and do a replace for JUST the deliminator. This should give you a concatenated string of all the values with a deliminator. This is much easier than other options.

Answer 2

If your looking for a pure Excel approach (ie no VBA) then the method proposed by James Jenkins is best. If you are happy using VBA then open up the VBA editor, add a new module, and add this code:

Option Explicit

Public Function JoinText(cells As Variant,Optional delim_str As String) As String
    If cells.Columns.count < cells.Rows.count Then
       JoinText = Join(WorksheetFunction.Transpose(cells), delim_str)
    Else
       JoinText = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(cells)), delim_str)
    End If
End Function

To open the VBA editor easily press Alt-F11. To insert a module you right-click on the workbook listed in the 'Project' window.

The function is called from excel as follows:

=JoinText(A1:C1)

If you want to add a delimiter (eg comma):

=JoinText(A1:C1,",")

The purpose of using the transpose function is to turn the 2d array, 'cells', into a 1d array. The reson for this is that the VBA function Join only accepts a 1d array. The reason for using two of them is if JoinText is looking at a row of cells (which is still just a 2d array) then the first call to transpose, transposes this 2d row array into a 2d column array, the second call turns it into a 1d array.

Answer 3

Shamelessly copied from this site:

1. Select the cell where you need the result.
2. Go to formula bar and enter ... "=A1:A5"
3. Select the entire formula and press F9 (this converts the formula into values).
4. Remove the curly brackets from both ends.
5. Add =CONCATENATE( to the beginning of the text and end it with a round bracket).
6. Press Enter.

What is particularly revelatory here is that when editing a formula, pressing F9 replaces the formula with the result of that formula. Where that's a range, it replaces it with a list of the contents of that range.

Answer 4

If you have Excel 2016, you can use an array formula:

Enter

=concat(a1:a255)

into the cell, then press

[ctrl]+[shift]+[enter]

Answer 5

*In a new tab, type A1 in cell A1,

*Type A2 in Cell A2

*Use fill series to complete the values in column A

*Type A1 in cell B1

• Use this forumal in cell B2

  =B1&","&A2

• Copy the formula down.

Copy and paste values to harvest the string of values you created.

A1  A1
A2  A1,A2
A3  A1,A2,A3
A4  A1,A2,A3,A4
A5  A1,A2,A3,A4,A5
A6  A1,A2,A3,A4,A5,A6
A7  A1,A2,A3,A4,A5,A6,A7
A8  A1,A2,A3,A4,A5,A6,A7,A8
A9  A1,A2,A3,A4,A5,A6,A7,A8,A9
A10 A1,A2,A3,A4,A5,A6,A7,A8,A9,A10

Answer 6

Press Alt-F11, insert new module, paste code bellow.

Public Function concatRange(data As Range, Optional sep As String = "") As String
    Dim ret As String
    Dim sep2 As String
    ret = ""
    sep2 = ""

    For Each cell In data
        ret = ret & sep2 & cell.Value
        sep2 = sep
    Next cell

    concatRange = ret
End Function

Usage:

=concatRange(A8:D11;", ")    'OS with ; list separator
=concatRange(A8:D11,", ")    'OS with , list separator or in a macro code

or

=concatRange(A8:D11)