Convert Word Table to Single Excel Cell with Linefeeds

I am receiving the results of a Web-based test via e-mail. I am able to paste the e-mail's contents into an Excel spreadsheet and use it to manipulate the data as necessary, with one exception, namely an answer that requires the test taker to write a multi-paragraph e-mail response. I am able to identify which cells in Column A this answer occupies, for sake of argument, A60 to A70.

I want to have the contents of these cells appear in a single cell, B60, with linefeeds. I did find an Excel macro on this forum:

Please Login or Register  to view this content.

This works after a fashion, but there are undesirable leading spaces at the beginning of each paragraph.

If I instead copy the cells into a Word document, I notice that they come through as a table. If I convert the table to text in Word, then paste it back into the formula bar of B60, I get what I want.

My question is, can this be done wholly within Excel, without the need for Word?

Thanks in advance for any help on this.

Burt Spielman
Madison, NJ

Originally Posted by lecxe

Hi Burt
Welcome to the board

Using your example, to copy the values in cells A60:A70 to B60, separated with linefeeds:

Please Login or Register  to view this content.

Does this help?

Yes! I did need to change "vbLf" to "chr(13)" in order to get the linefeeds to show properly. Perhaps it's because I'm using Excel 2011 on a Mac and the coding is slightly different.

Thanks for your help.

OK, this scheme has been working well for me, but I have run into one minor glitch: Sometimes the values (as above) of "indirect(e1)" and "indirect(e2)" are identical. In this case the macro fails. How can I accommodate this instance?