Batch string concatenation in Excel

excel excel-formula concatenation string-concatenation
Green Demon picture Green Demon · Feb 28, 2013 · Viewed 55k times · Source

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

James Jenkins picture James Jenkins · Feb 28, 2013

*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

Related questions

Putting a concatenate inside of a count if

Alright, I have two excel functions that work. I want to combine them into one. Here are the two that work: =COUNTIF('ALL EE Active BEG PERIOD'!$A:$B, 'HC Summary Details'!$A6) =CONCATENATE('ALL EE Active BEG PERIOD'!A2," …

Read More
Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More