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

Thank you so much for sending me this formula. It worked perfectly and did
exactly what I needed. I really appreciate your quick, helpful response.

Sincerely,
Michelle

"Peo Sjoblom" wrote:

> 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
>
> "Batmans_Wife" wrote:
>
> > 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.

To concatenate in place, you can use a macro like the one found here:

http://www.mcgimpsey.com/excel/mergedata.html

In article <960DC31C-EDD3-4871...@microsoft.com>,

Thanks for sending the link to your page with the macros and instructions.
It's very detailed and thorough. Although I was able to figure it out with
the formula that Peo posted earlier, I appreciate your further information
and will keep it handy for future use.

Sincerely,
Michelle

Hi there
But I don't want formulas on the page..
As Well I am merging cell c and d and want the info to stay in d
I will end up copying and pasting to another program

Thanks
Sandra

I think I'd insert a new column E and put the formula there.

You could copy that range to the other program.

Or you could convert that new column E to values and delete columns C:D (or just
column D???).

--

Dave Peterson

The answer to my prayers! How do you convert the column into values?

Select the range to convert to values
Edit|Copy
Edit|Paste special|Values

--

Dave Peterson

See answer at your other post.

Gord

On Mon, 28 Apr 2008 05:52:01 -0700, Angie <An...@discussions.microsoft.com>
wrote:

Thank you so much for sending me this formula.I really appreciate your
quick, helpful response.

Sincerely,
roshni

Dave, Thanks, just found your reply to the other poster -
I have been trying to do this for hours - I knew it had to be possible
thank you thank you

Thank goodness for Google <vbg>!

--

Dave Peterson

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?

Not without a User Defined Function like this one.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " "
' for comma-delimited change above " " to ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =concatrange(A1:Z1)

Nore: blank cells will be ignored.

For similar methods with code see this search result from google

http://tinyurl.com/6ao6k4

Gord Dibben MS Excel MVP

Im still stuck on trying to concatenate a wide range of cells. I tried the
formula
=concatrange(K2:KZ2)
but get the following:
#NAME?

When I try to search help for concatrange, nothing comes up, only
concatenate.

Could you clarify more for me please? Thank you.

Concatrange was a user defined function which Gord included in his message.
You won't find it in help as it isn't a native Excel function.
Have you included that UDF?
--
David Biddulph

"anthony561fl" <anthon...@discussions.microsoft.com> wrote in message
news:C511EEEC-CD18-498A...@microsoft.com...

You cannot concatenate a range of cells using the Excel CONCATENATE
function.

Either =CONCATENATE(K2,L2,M2) etc. or =K2&L2&M2 etc.

or a User Defined Function

' the " " returns space-delimited text

Usage is: =ConCatRange(K2:KZ2)

If you're not familiar with VBA and macros/functions, see David McRitchie's
site for more on "getting started".

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

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

In a cell enter the formula as shown above in Usage is:

I am trying to do the same thing; merge cells without deleting the data but
when i enter the formula below, a "0" appears. What am I missing?

"Peo Sjoblom" wrote:

What do you have in A1 and B1?

Works perfectly Gord - and please excuse my noobness, but how do I make the
function available to all spreadsheets that I open without have to recreate
the function each time?

Ray D

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

On Wed, 5 Nov 2008 06:22:00 -0800, rapid1 <rap...@discussions.microsoft.com>
wrote:

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

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.

On Fri, 7 Nov 2008 02:42:00 -0800, Oli <O...@discussions.microsoft.com>
wrote:

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!

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

what if you wanted to do multiple rows?

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.

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

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

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

Thanks

Good luck on the rest.

Thank you Peo!

That's a great formula!

-Jim

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

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

Please read again what Peo told you.
--
David Biddulph

"bonnie" <bon...@discussions.microsoft.com> wrote in message
news:CC86DE1A-2A1B-4D68...@microsoft.com...

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

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.

--

Dave Peterson

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

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.

"Peo Sjoblom" wrote:

You can use:

=A1&CHAR(10)&B1

Be sure to format the cell as having wrapped text, otherwise you'll
just see a small symbol between the values from A1 and B1.

--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192711

http://www.thecodecage.com/forumz

This worked perfectly, however, I now need to put a comma between the two
names - i.e. currently I have Smith John in one cell, and need Smith, John in
one cell. Is there a way to do that?

When all else fails, think about it....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dgui...@gmail.com
"Stephie" <Ste...@discussions.microsoft.com> wrote in message
news:AA47D297-9932-4280...@microsoft.com...

As long as your original data is still there, you can use another formula:

=A1&", "&B1

If all you have is the cell with the result, you could use:

=substitute(a1," ",", ")

Stephie wrote:

--

Dave Peterson

That works except now i have a lot of spaces between the information. How
would you get rid of those spaces

Then use this instead.

=A1&B1

Hi Gord,
Is there a way I can specify so that the function will:
a) change the results from target cell to next door neighboring cell as values
b) Separate out each string as a line within the same cell, as if I had used
the alt+Enter option myself?

Thanks,

2. Change the de-limiter from " " or "," to Chr(10) and set the cell to
wrap text.

If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Chr(10)

1. The function cannot copy anything to anywhere.

Functions return results to the cell in which they are written.

You can manually Copy and paste special>values to next door cell.

Gord

Thanks Gord...

"Gord Dibben" wrote:

> .
>

I'm not sure what I'm doing wrong. I highlight the two cell, ctr C, move to a cell to the right, Edit|Paste Special|Value only, and both cells are copied to the two cells where I move my focus. It just duplicates the two cells. It doesn't combine the contents into the one new cell

Hmmm,