How do I merge two cells without deleting data from the cell?

[Gord Dibben]

You can copy the UDF into a general module in your Personal.xls which will
make it available for all open workbooks.

You could copy/paste it into a general module in a new workbook, save that
workbook as an Add-in which you would load through Tools>Add-ins.

I prefer the Add-in method so's I don't have to preface the Function with
the workbook name.

i.e. if saved in Personal.xls you must enter

=Personal.xls!ConCatRange(range)

If stored in the add-in =ConCatRange(range) is sufficient.


Gord

 

[Oli]

Hi,
I want to merge two columns...not two cells only...i have values in column B
and C and I like to merge them together in one go...the number of values I
got in the columns are more than 19,000 (i.e. I have 19000 rows of data)

Can anyone help plz?

Thanks in advance
Oli

 

[Gord Dibben]

Enter the formula provided by Peo into C1 then double-click on the fill
handle of C1 to copy to end of data in column B

BTW.............this operation would not be "merging" the columns, it would
be "combining" them.


Gord Dibben MS Excel MVP

 

[j9]

Thanks that was helpful, but how do you continue the formula for a lot of
rows. It keeps repeating the 1st one down.

Thank you!

 

[j9]

is it the same formula for all the rows...it keeps repeating the same thing
all the way down...an ideas?

 

[j9]

what if you wanted to do multiple rows?

This works great. However, what if Im wanting to combine several columns, say
50 or 100 columns worth of data? Id hate to have to enter each cell name in
that formula. Is there a way to specify a range of columns or cells rather
than each one before and after ampersands?

 

[Gord Dibben]

Browse through these postings to which you tacked onto.

http://tinyurl.com/599r3q

Make sure that your calculation mode is set to automatic so's you don't get
same results all the way down.


Gord Dibben MS Excel MVP

 

[AcousticVin]

The formula worked perfectly for me as well...thanks for the post.

 

[MK]

try instead of a1&" "$b1":
$A1&" "&$B1
then drag/fill the cell however many rows/cols you want to do

the "$" makes it absolute and fill wont change the valu immediatly preceded
by the "$" so "A" and "B" will never change but "1" in both cases will

 

[MK]

Thankyou Gord I tried to write this code my self and failed your sorce code
is what i needed to get started I wish Microsoft wold make this a standard in
thier formulas as I find it very useful I still need to modify it slightly to
work with more applications but at least I have the start I needed and have
you to thank for that

 

[Gord Dibben]

Thanks

Good luck on the rest.

Thankyou Gord I tried to write this code my self and failed your sorce code
is what i needed to get started I wish Microsoft wold make this a standard in
thier formulas as I find it very useful I still need to modify it slightly to
work with more applications but at least I have the start I needed and have
you to thank for that

 

[Jim (mcse03]

Thank you Peo!

That's a great formula!

-Jim

You can't, you can concatenate 2 cells into one by using a formula and
ampersand

=A1&" "&B1


however stay away from merging cells, always cause more problems than what
it's worth and layout wise you can get very close without using it. I have
never seen a power user using merging

Regards,

Peo Sjoblom

 

[Muhammad Javaid Hassan]

I'm highlighting two cells in the same row, hitting format cells, alignment,
merge cells and I'm getting the error message, "The selection contains
multiple data values. Merging into one cell will keep the upper-left most
data only." I want to be able to make the two seperate cells one without
deleting anything and without having to cut and paste.

 

[KHaberwoman]

I am trying to merge 3 columns into one to put on a mailing label. I am
trying to concantinate and my formula is =("A2,&B2" ",&C2")

Only the Middle field does not already have an empty character at the end of
the cell so they don't need a blank space in between. I want my results to
be: To the ________ Family

Problem: I haven't tried this since upgrading to Office 2007 and I get the
formula in the cell but not the results? What am I doing wrong?

Thanks

 

[bonnie]

How do you merge two calls when one row contains the same information and the
second row contains different information

 

[David Biddulph]

Please read again what Peo told you.

 

[GetWet]

I'm new to this... where do I place the formula?
Thanks,

 

[Dave Peterson]

Any cell that you want--except for A1 and B1.

I like to insert a new helper column (Maybe column C in this case, so it's close
to the data), then use the cells in that new column.

 

[Syed Wahaj Ul Hasan]

wow! many thanks... it worked perfectly fine and solved my problem. Best
Regards, Wahaj

 

[Joe]

Are there any characters that can be added between the &" and the "& so that
the combined cells can appear as a list instead of a line? For instance, I
have two sentences in two different cells. I want to put in one cell, but
have one above the other with spacing in between.