Excel Spreadsheet help?

[ckret01]

Does anyone know how to combine "several" cells into one? So that all the information is grouped together in 1 cell?

We have a spreadsheet that has a row in column A from A1 through A200 that we need all in 1 cell. Anyone know the formula?

 

[Cindy B]

There is a key that combines and centers all information. In my Excel, there is a key with rectangle with an E or something like that.

I used that today to combine and center my info. It centered about 4 or 5 cells.

 

[chell]

Do you want to add the data to get a sum or do you want to merge the cells?

If you want a sum type: =SUM(A1:A200)

 

[ckret01]

Just want to merge all those cells into 1? Dont want to add the information together.

it looks like this- eah in its own cell in the same column of the spreadhseet

Jen
Bob
Bill
Larry

But now I want to combine those individual cells so all the names are now in 1 cell instead of serparate:
"jen bob bill larry"

does that make sense

 

[doxdogy]

To merge a cell in Excel, first highlight the cells that you want to merge. Then go to format on the toolbar, click on that and select cells. This will open a pop up box, choose the tab alignment, you will then see three small boxes, check the merge cells box. Also look to see if there is a box on the toolbar with a centered a in it. This the button for merging cells in Excel.

eta: You may want to merge the cells before adding the information. Because with Excel you can loose most of the information after you merge the cells together.

 

[ckret01]

Thanks for the advice but for some reason when I try to merge the cells the way you explained it only keeps one of the names instead of all of them.

 

[oogieboogie]

if you want to keep the names in one cell, what you need to do is:

go to an empty cell and write the formula:

=A1 & A2 & A3 & A4

if you want a space in between the names you need formula:

=A1 & " " & A2 & " " & A3 & " " & A4

that will combine them in one cell

 

[ckret01]

Thank you for the formula.....is there a way to do that but without typing all 200 cells (example:a1,a2,a3 and so on) to eliminate having to do that , is there a formula that does from a1 to a200 without typing all 200 in?

Sorry for all the ??'s I apprecaite everyone's help

 

[lapinluv]

I usually cheat. Copy the cells to Word. Then choose Convert Table to Text. Copy the text back to Excel into one cell.

 

[ckret01]

I am trying that now, I copied my info into word , where so I find how to convert the table into text?

 

[LisaZoe]

Originally posted by lapinluv
I usually cheat. Copy the cells to Word. Then choose Convert Table to Text. Copy the text back to Excel into one cell.

That's probably how I'd do it too. I don't consider it cheating, though, just being creative.

 

[schmoopie]

I don't know if this is much faster, but...you can use the formula
=CONCATENATE(a1,a2,....)

Click on the first cell and type a comma, click on the second cell (comma) third cell (comma)...

Of course I think the function says it only works up to 30 text strings, so essentially you'd have to do it 6 times of 30 and then combine those strings.

Not sure that really helped.

 

[ckret01]

LOL....ok I converted the table into text but when i highlighted the info and tried to paste it back into excel into that one cell it pasted it all in separate like it was-LOL

 

[Loubon]

The function in Excel you want is Concatenate. There are two limitations but they are not bad.

First you can only do 30 cells at a time. So if you do 6 groups of 30 plus your last group of 20 you will then have 7 merged cells which you can then merge separately (since they are less than 30)

Second you do need a formula that says =concatenate(a1,a2,a3 etc.) To avoid all of the typing click your function key (fx) and select the concatenate function. It will then list 30 spaces. Point to a space and then click a cell (example point to text 1 and then pick a1). Shouldn't take you more than 10 minutes tops. No typing just clicking.


Hope this makes sense.

 

[LisaZoe]

Originally posted by ckret01
I am trying that now, I copied my info into word , where so I find how to convert the table into text?

Highlight what you want to convert, go to the Table tab, go down to Convert and select Table to Text. All you then need to do is select the delimiter (I'd suggest paragraph). You'll then end up with each name on a separate line so you have to replace all the paragraph characters with a space. I do this by highlighting the text, pushing CTL & H keys at the same time, then enter the token for paragraph (^p) in the first line and whatever you want to use instead in the second line (such as a comma and space). Replace all and copy the contents to the cell.

BTW - I did a very quick test of these instructions as I wrote them and it worked. You probably got them in seperate cells again if you did not replace the paragraph mark.

 

[ckret01]

Thank you all soooooooo very much. I knew coming here I would get the answer. I just love this board. Never had this many friends