Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old May 2nd 08, 10:09 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 1
Default How do I merge two cells without deleting data from the cell?


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

Sincerely,
roshni
"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.


  #12   Report Post  
Old May 21st 08, 02:04 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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

"Dave Peterson" wrote:

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



Angie wrote:

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

"Dave Peterson" wrote:

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 (or just
column D???).

xjaysfan wrote:

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

"Batmans_Wife" wrote:

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.

--

Dave Peterson


--

Dave Peterson

  #13   Report Post  
Old May 21st 08, 03:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How do I merge two cells without deleting data from the cell?

Thank goodness for Google <vbg!

kimceee wrote:

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

"Dave Peterson" wrote:

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



Angie wrote:

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

"Dave Peterson" wrote:

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 (or just
column D???).

xjaysfan wrote:

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

"Batmans_Wife" wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #14   Report Post  
Old August 6th 08, 02:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 2
Default How do I merge two cells without deleting data from the cell?

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?



"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.

  #15   Report Post  
Old August 6th 08, 09:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default How do I merge two cells without deleting data from the cell?

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)

No 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

On Wed, 6 Aug 2008 06:46:01 -0700, anthony561fl
wrote:

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?



"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.




  #16   Report Post  
Old August 10th 08, 04:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 2
Default How do I merge two cells without deleting data from the cell?

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.




"roshni" wrote:


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

Sincerely,
roshni
"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.

  #17   Report Post  
Old August 10th 08, 05:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default How do I merge two cells without deleting data from the cell?

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" wrote in message
...
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.




"roshni" wrote:


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

Sincerely,
roshni
"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.



  #18   Report Post  
Old August 10th 08, 05:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default How do I merge two cells without deleting data from the cell?

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

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 & " "
' the " " returns space-delimited text
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

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 InsertModule. 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:


Gord Dibben MS Excel MVP


On Sun, 10 Aug 2008 08:49:00 -0700, anthony561fl
wrote:

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.




"roshni" wrote:


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

Sincerely,
roshni
"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.


  #19   Report Post  
Old September 17th 08, 08:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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:

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.

  #20   Report Post  
Old September 18th 08, 12:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default How do I merge two cells without deleting data from the cell?

What do you have in A1 and B1?


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 12:44:01 -0700, Geejeta
wrote:

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:

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
Skipping cells while reorganizing data vertblancrouge Excel Discussion (Misc queries) 0 April 25th 05 07:26 PM
Deleting #N/A from cells... Jambruins Excel Discussion (Misc queries) 3 February 23rd 05 12:36 AM
moving data in excel without deleting existing data jigna Excel Discussion (Misc queries) 1 January 30th 05 12:35 PM
cells lose relativity to other WorkSheets after data sort? Ed Murray - ALPCO Excel Worksheet Functions 1 November 8th 04 08:02 PM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017