Excel 2007 : Copy text from multiple cells into one cell

[neflhiem]

Problem. Im trying to convert a .Doc question bank into an excel spreadsheet that i can use for flash cards. The format i need the spreadsheet in is

A ............. B
Question....Answer

basically. Well what i have now is the imported word doc with all questions and answers all in consecutive rows. The catch is some of the answers are mulit lines A, B, C and or D all of the above (and i need to see all of the above) so to say
So it looks like this:
A
Which of the following are letters
A. A
B. 2
C. C
D. A and C

and I need it to look like

A....................................................B
which of the following are Letters........A. A
.......................................................C. C
.......................................................D. A & C

where all of that info is in Row 1.

So i need to be able to select multiple cells, copy the text and paste that text into 1 cell. I have tried the past special/values but that still puts the data into multiple rows. I dont think a formula will work because i will have to re-write it every time and thats just as much work as copying the text only and pasting it for each row.

I have 439 Questions to do this for and some are only one line where others are up to 4 lines in the ABCD format.

there surely is a simple way to copy text only ... any help would be great.

thanks all.

[mrice]

Suggest that you post a copy of the sheet to get more specific help.

[neflhiem]

ok.. here is the spreadsheet in its entirety, you can see Question 1 is formatted the way I want, if you look at question 3 you see that it is not.

Again i need to past data from multiple cells into one cell. Any help or ideas would be great.

[neflhiem]

hmm.. no ideas?

[mrice]

Try the attached.

I've mapped control l to a special copy function which copies all selected cells to the clipboard as a single string.

Does this help?

[Confused2]

Have you tried working with the CONCATENATE function?

From your example above,

=CONCATENATE(B1,B2,B3) will give you: A. AC. **. A & C, in a single cell.

[angExcel-entflea]

Would this work in copying multiple select cells from Excel 2007 and paste those into Windows Notepad?

[Glenn Kennedy]

What happened when you tried it? If it didn't work....


It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.