CODEWITHSUNDEEP
excelexcel-formulaconcatenationstring-concatenation
Green Demon
Green Demon

Reputation: 4208

Batch string concatenation in Excel

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.

Upvotes: 30

Views: 56890

Answers (12)

Steve Melnikoff
Steve Melnikoff

Reputation: 2670

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.

Upvotes: 0

John Lehrkind
John Lehrkind

Reputation: 53

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.

Upvotes: -1

user6654769
user6654769

Reputation:

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

Enter

=concat(a1:a255)

into the cell, then press

[ctrl]+[shift]+[enter]

Upvotes: 1

E.V.I.L.
E.V.I.L.

Reputation: 2166

concatenate(a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27, a28, a29, a30, a31, a32, a33, a34, a35, a36, a37, a38, a39, a40, a41, a42, a43, a44, a45, a46, a47, a48, a49, a50, a51, a52, a53, a54, a55, a56, a57, a58, a59, a60, a61, a62, a63, a64, a65, a66, a67, a68, a69, a70, a71, a72, a73, a74, a75, a76, a77, a78, a79, a80, a81, a82, a83, a84, a85, a86, a87, a88, a89, a90, a91, a92, a93, a94, a95, a96, a97, a98, a99, a100, a101, a102, a103, a104, a105, a106, a107, a108, a109, a110, a111, a112, a113, a114, a115, a116, a117, a118, a119, a120, a121, a122, a123, a124, a125, a126, a127, a128, a129, a130, a131, a132, a133, a134, a135, a136, a137, a138, a139, a140, a141, a142, a143, a144, a145, a146, a147, a148, a149, a150, a151, a152, a153, a154, a155, a156, a157, a158, a159, a160, a161, a162, a163, a164, a165, a166, a167, a168, a169, a170, a171, a172, a173, a174, a175, a176, a177, a178, a179, a180, a181, a182, a183, a184, a185, a186, a187, a188, a189, a190, a191, a192, a193, a194, a195, a196, a197, a198, a199, a200, a201, a202, a203, a204, a205, a206, a207, a208, a209, a210, a211, a212, a213, a214, a215, a216, a217, a218, a219, a220, a221, a222, a223, a224, a225, a226, a227, a228, a229, a230, a231, a232, a233, a234, a235, a236, a237, a238, a239, a240, a241, a242, a243, a244, a245, a246, a247, a248, a249, a250, a251, a252, a253, a254, a255)

PowerShell it!

"concatenate(a$((1..255) -join ', a'))" | clip

Open the text file copy and paste

"To quickly select cells you can press CTRL and click on cells you want to be included in the concatenate function. Example, Select a cell Type =concatenate( in formula bar Press and hold CTRL button and click cells to be included. Release CTRL button Type ) in formula bar and press Enter"

Upvotes: 6

dedek
dedek

Reputation: 8301

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)

Upvotes: 13

Fraser Porter
Fraser Porter

Reputation: 1

where the values that you would like to concantenate start in row 2 column 3 of your sheet

Sub GOWN()
roww = 2
Do While cells(roww, 2) <> ""
    aa = cells(roww, 3)
    dd = dd & aa & ","
    roww = roww + 1
Loop
cells(roww + 1, 3) = dd
End Sub

Upvotes: 0

Ronald Bourret
Ronald Bourret

Reputation: 41

It isn't purely Excel, but there is an easy way to do this with Word.

  1. Select the cells you want to concatenate and copy/paste them into Word. This creates a table.
  2. Select the entire table.
  3. Convert the table to text. Use paragraph marks (or something else that does not appear in your text) as separators.
  4. Select all of the text.
  5. Use Replace to remove the paragraph marks. (In the "Find what" box, enter ^p. Leave the "Replace with" box empty.)

Upvotes: 1

kmort
kmort

Reputation: 2928

My preferred method is to cut-and-paste the values into an editor that allows regular expressions, then I simply remove the tabs (or spaces) with a find and replace on my current selection.

You can also use this to insert commas, whitespace, or whatever you want.

It's a ton faster than typing =concatenate(A1,",","A2",",",......)

Upvotes: 2

user425678
user425678

Reputation: 760

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.

Upvotes: 2

Marc
Marc

Reputation: 11613

See this blog post here: http://www.dullsharpness.com/2011/11/14/excel-vba-range-to-csv-range2csv-function/

You can use it like so, e.g. with a pipe delimiter:

=Range2Csv(A1:A255,"|")

Access your VBA editor using Alt+F11 and drop it into a module.

Code excerpt is here:

Option Explicit
'**********************************************
'* PURPOSE: Concatenates range contents into a
'*          delimited text string
'*
'* FUNCTION SIGNATURE: Range2Csv(Range, String)
'*
'* PARAMETERS:
'*    Range  - the range of cells whose contents
'*             will be included in the CSV result
'*    String - delimiter used to separate values
'*             (Optional, defaults to a comma)
'*
'* AUTHOR: www.dullsharpness.com
'*
'* NOTES: [add'l notes removed for brevity]
'*
'**********************************************
Public Function Range2Csv(inputRange As Range, Optional delimiter As String)
  Dim concattedList As String 'holder for the concatted CSVs
  Dim rangeCell As Range      'holder cell used in For-Each loop
  Dim rangeText As String     'holder for rangeCell's text

  'default to a comma delimiter if none is provided
  If delimiter = "" Then delimiter = ","

  concattedList = ""          'start with an empty string

  'Loop through each cell in the range to append valid contents
  For Each rangeCell In inputRange.Cells

    rangeText = rangeCell.Value 'capture the working value

    'Only operate on non-blank cells (i.e. Length > 0)
    If Len(rangeText) > 0 Then
      'Strip any delimiters contained w/in the value itself
      rangeText = WorksheetFunction.Substitute(rangeText, delimiter, "")

      If (Len(concattedList) > 0) Then
        'prepend a delimiter to the new value if we
        'already have some list items
        concattedList = concattedList + delimiter + rangeText
      Else
        'else if the list is blank so far,
        'just set the first value
        concattedList = rangeText
      End If
    End If

  Next rangeCell

  'Set the return value
  Range2Csv = concattedList

End Function

Upvotes: 8

James Jenkins
James Jenkins

Reputation: 1954

*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

Upvotes: 35

chuff
chuff

Reputation: 5866

This VBA function will concatenate the contents of cells, with an optional delimiter, if needed. Copy it into a standard module:

  Option Explicit

  Function Concat(CellRange As Range, Optional Delimiter As String) As String 
 ' this function will concatenate a range of cells and return the result as a single string
 ' useful when you have a large range of cells that you need to concatenate
 ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If Delimiter = Null Then
      dlm = ""
  Else
    dlm = Delimiter
  End If
  For Each cell In CellRange
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
      retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
End Function

Upvotes: 3

Related Questions