Excel nerds, help a sister out (please). Need to get multiple rows in one cell.

Factual Questions
21

I think a lot of people are missing that you want to concatenate values in rows, not columns. Copy/pasting to use the same formula for multiple cells will work if the relative position of your formula to the source cells is the same, but it’s tough to suggest an approach without knowing the complete layout of your spreadsheet.

I’d recommend trying Jack Batty’s suggestion of saving as CSV, but with an additional step up front. Select all the rows you want to concatenate, copy, then choose Paste Special and select the Transpose option. This will put the rows into columns so you have the right values all together.

22

Agreed. I had seen the reference to concatenating from rows rather than columns, but as **TroutMan **says, without knowing the structure of your spreadsheet and exactly what you’re trying to do, it’s hard to give more specific advice.

23

Hmm, I tried Jack’s suggestion, transposed first to get the data in rows, but it still separates the info into separate cells.

I have a bunch of information in a column, each row has text in it. I want each entry from each row to be in one cell, separated only by semicolons. I don’t want to pull information from other columns. There’s only one column I’m dealing with and I want all of its contents, that is each entry from each row, to be in a single cell with a semicolon between each entry.

So instead of:


A
1
2
3
4

Etc, I want:


A
1; 2; 3; 4

Funny thing is I don’t need to know this anymore, really, but I’m curious and need to know so I can die a happy woman.

24

I should say the data is now in a row with each entry in a separate column.

25

Okay, that makes a lot more sense now. You actually only want one cell, with all the values concatenated. I would set up column B to be equal to the equivalent cell in col A plus whatever separator you want, e.g. the formula for cell B1 should be


=A1 & "; "

Note the extra space after the semicolon there, before the double quote. Drag that formula all the way down to the bottom of the range that contains the data, and you should have something like this:



A  B
1  1;
2  2;
3  3;
4  4;

Then pick the cell where you want the final string to appear, and set the formula there equal to

=CONCATENATE(B1, B2, B3…)

until the end of the range. I’m afraid I don’t know any way to automate that process of filling in the formula other than typing it out, but at least this way you don’t have to also enter the semi-colons into the final concatenation.

26

Hmmm. Other, wiser people than I have had a crack at this and come up with less painful solutions. I’m not surprised. You might want to try the method outlined here (http://gotochriswest.com/blog/2011/05/20/concatenating-a-range/), shifting everything over to the right by one column, and using the with-a-semicolon column B that I described above.

27

Was the problem you were trying to avoid having to manually create a formula with so many cells? Dervorin’s suggestion is fine if you have a dozen or so cells where it isn’t too onerous to type B1, B2, B3, etc.

But if you have like 50 cells you want to concatenate, add a formula in B1 next to the first cell: =A1&"; “. Then in cell B2 add a formula =B1&A2&”; ". Copy that formula down through all the rest of the cells, and the final cell will have the complete formatted list.

ETA: or exactly like Dervorin linked to above. (Does that make me a wise person?)

28

Sucess! Yes, the problem was I didn’t want to write out a long ass formula for a godzillion cells. This worked great. Thanks!

29

Just wanted to add: in case you are stuck on a related search in the future, the phrase that brought me to the Magical Fairy Land of Googly Goodness was “excel concatenate range”. This may prove useful to you some day, in some related set of circumstances, perhaps.

30

It blows my mind that =concatenate(B1:B50) doesn’t work. Of course, I also don’t understand why I can use Paste Special if I’m copying but not if I’m cutting. Clearly I am not meant for excel.

31

Danggit, I’m a geek…
I don’t do anything fun excel in excel anymore, so this entrigued me.

Assuming data is in A1-A50;
Select cell B2.
Type =A1:A50&", " in formula bar. Note the space between the comma and the closing parenthesis
. Depending on your regional settings, you may need to use: =TRANSPOSE(A1:A50)&", " Press F9.
Delete curly brackets in formula bar.
Delete last delimiting character.
Type =Concatenate( in front of all characters in formula bar.
Type ) after last character in formula bar.
Press Enter
The cell data is concatenated w/ comma & a space seperators in B2

32

Neat trick, I hadn’t seen that before. It’s not appropriate if you want the concatenation to be recalculated/refreshed when the source data changes, but for something like this it’s very useful. Thanks for the tip.

33

Here’s my suggestion:
You have:
A
1
2
3
4
5
6

So in B: put formulas
=A1
=CONCAT(B1, "; ", A2)
=CONCAT(B2, "; ", A3)
=CONCAT(B3, "; ", A4)
etc.

You can get the repeating formula (after the first row) same as mentioned for the others by dragging. The references to cells will adjust accordingly.

Basically - “The string above and add the cell at the left”.

The last cell should hav the result; convert to value with paste special.

34

Brilliant! Very easy so even I can do it.

Pretty similar to an earlier suggestion, but requires me to type fewer characters, which is always welcome!

These shortcuts are awesome for very long lists, but if you want to concatenate only four or five cells, copying and pasting might be faster. I wish Excel had a tool that allowed you to select the cells you’d like to concatenate, enter your delimiting character of choice, click OK, et voila. I also wish I had a pony.

Anywho, thanks guys! You’re all aces in my book.