Excel help... any way to consolidate values in multi cells into one?

[KevC]

Hey,
I've got Microsoft Excel 2003. I am working on organising tons of data (words, not numerical) that's given to me in a bunch of spreadsheets and I need to consolidate all of them into one. The original spreasheets are not organised very well, so a writing of a macro isn't an option. Copy and paste it is. However, there are some sections where I need to combine the information in multiple cells into one.

Example:
cell1: John
cell2: is the
cell 3: manager of the
cell4: company.

Now it'd be really nice if there was some sort of way to copy cell1-4 and paste it into cell 5: John is the manager of the company. Anyone know a way possible? It's taking me forever since I have to go into the fx, not just copy/paste the cell.... not to mention that there are some instances where I have to combine 10+ cells into 1.

Thanks...

 

[arkamw]

This problem has been around for a long time. I know that on anything pre-2k3 it was a little more difficult as copy/paste, of course, doesn't work exactly as you need it to in this instance.

If the cells are right next to each other, you might be able to just merge all the cells together. You can't do this in 2k as it will erase all but the first cell.

The other thing you could probably play around with is the concatenate function (at least, I think that what it is). You'll have to google for exact formulas but I think it's something like con(cell1, cell2, etc). It's been a while since I tried that. Downside to this is if you have a bunch of cells in a lot of random postions, you'll end up doing a lot more formula writing than actual copying.

Or, could you export the excel document as a word file? Then you could take out all the spaces / tabs with the replace function. Take a little time, but it'd probably work.

 

[eeyrjmr]

in cell5 just do

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

assuming the data is in A1,A2...

this is easy enough to expand to more.

Once finished you can then select all and copy and the "paste special" to paste JUST the value and NOT the formula