How to merge columns and rows into one cell besides using Merge and Center Icon?

[valerie]

I had posted this question before, but I couldn’t find this thread in
any of the pages up till page 17. So I’m posting again.

I want to merge all rows and columns starting from A1 to J2, with no
lines in between into one cell. My text data value are in D1 and D2
respectively. Using Merge and Center Icon will only retain the
upper-left most data, resulting the data in D2 to be deleted. So how to
merge all rows and columns into one cell and yet prevent the data in D2
to be deleted?
Any help will be greatly appreciated.

 

[J.E. McGimpsey]

First - I assume that "page 17" is in whatever web portal you're
using to read this newsgroup. If you can't find your post there, you
can always check the archives:

http://google.com/advanced_group_search?q=group:*excel*

Use your name or email address in the Author field. It generally
takes 4-8 hours for a post to show up in the archives after you post
to the group.

In this case, here's your earlier post, with one reply:

http://google.com/[email protected]
m


(watch linewrap: the URL ends with ".com")

 

[Ken Wright]

The other way besides JEs code is to use a formula, though I may be reading your question
incorrectly. You don't say which order you want it done in, ie A1 B1 C1 D1 etc, or A1 A2 B1 B2 C1
C2 etc, but either way, if what you are looking to do is to merge all the data in the *range*
A1:J2, then a simple =A1&B1&C1&D1 etc or =A1&A2&B1&B2 etc will do that for you

 

[valerie]

J.E, I've read ur reply and have seen the information in ur url address
stated in it. But I do not really understand it, cos it looks
complicated to me. In short, I would like to ask, do I really have to
use a macro in this case? If yes, then dont mind could you explain it
briefly as how to go about using this feature here?
Thank you very much.

 

[J.E. McGimpsey]

As I understand your problem, a macro is the only way to merge your
cells while retaining the values from each cell.

Check out David McRitchie's Getting Started with macros page:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The macro itself is fairly straightforward: it takes the text value
of every cell in the selection and concatenates them to form a
string. It then merges the cells and puts the concatenation in the
first cell.

Its usage would be:

-attach the to a button, menu item or keyboard shortcut, make
your selection, then click the button, menu item, or type the
shortcut.
or

- select your cells, then choose Tools/Macro/Macros and type the
name of the macro into the textbox. Click Run.

If after reading David's page you're still confused, post back with
specific questions about what you want, what you've tried, and what
is or isn't working.