Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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.

  #2   Report Post  
Batmans_Wife
 
Posts: n/a
Default

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
xjaysfan
 
Posts: n/a
Default How do I merge two cells without deleting data from the cell?

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I merge two cells without deleting data from the cell?

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???).

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default How do I merge two cells without deleting data from the cell?

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:D (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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I merge two cells without deleting data from the cell?

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:D (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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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:D (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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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,
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

See answer at your other post.


Gord

On Mon, 28 Apr 2008 05:52:01 -0700, 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:D (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


  #10   Report Post  
Banned
 
Location: India
Posts: 1
Default

You can also copy and paste the one cell content to another cell and then merge together or use the Wrap text feature.


  #11   Report Post  
Junior Member
 
Posts: 27
Default How do I merge two cells without deleting data from the cell?

The fastest and easiest way to combine two or more cells in Excel is to use the built-in Merge and Center option. The whole process takes only 2 quick steps:

Select the contiguous cells you want to combine.
On the Home tab Alignment group, click the Merge & Center
Once you click Merge and Center, the selected cells will be combined into one cell and the text is centered
Other merging options in Excel
To access a couple more merge options provided by Excel, click the little drop-down arrow next to the Merge & Center button and choose the option you want from the drop-down menu:

I hope this helps!
Tim Paine
  #12   Report Post  
Posted to microsoft.public.excel.misc
J9 J9 is offline
external usenet poster
 
Posts: 7
Default How do I merge two cells without deleting data from the cell?

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

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

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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.

  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
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.


  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default How do I merge two cells without deleting data from the cell?

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

"Gord Dibben" wrote:

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?



  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

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 ToolsAdd-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
wrote:

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

"Gord Dibben" wrote:

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?


  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default How do I merge two cells without deleting data from the cell?

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,

"Gord Dibben" wrote:

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 ToolsAdd-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
wrote:

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

"Gord Dibben" wrote:

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?



  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

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 specialvalues to next door cell.


Gord

On Wed, 2 Jun 2010 11:15:12 -0700, GonzaloRC
wrote:

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,

"Gord Dibben" wrote:

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 ToolsAdd-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
wrote:

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

"Gord Dibben" wrote:

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?




  #19   Report Post  
Posted to microsoft.public.excel.misc
MK MK is offline
external usenet poster
 
Posts: 57
Default How do I merge two cells without deleting data from the cell?

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

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.



  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

Thanks

Good luck on the rest.

On Thu, 29 Jan 2009 10:50:01 -0800, MK wrote:

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

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.






  #21   Report Post  
Posted to microsoft.public.excel.misc
J9 J9 is offline
external usenet poster
 
Posts: 7
Default How do I merge two cells without deleting data from the cell?

what if you wanted to do multiple rows?

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

  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

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

On Fri, 12 Dec 2008 18:15:02 -0800, j9 wrote:

what if you wanted to do multiple rows?

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


  #23   Report Post  
Posted to microsoft.public.excel.misc
MK MK is offline
external usenet poster
 
Posts: 57
Default How do I merge two cells without deleting data from the cell?


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

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

  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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.

  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
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.




  #26   Report Post  
Posted to microsoft.public.excel.misc
Oli Oli is offline
external usenet poster
 
Posts: 5
Default How do I merge two cells without deleting data from the cell?

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



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

  #27   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I merge two cells without deleting data from the cell?

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


On Fri, 7 Nov 2008 02:42:00 -0800, Oli
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



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


  #28   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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

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

  #29   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

Thank you Peo!

That's a great formula!

-Jim

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

  #30   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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

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



  #31   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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



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

  #32   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I merge two cells without deleting data from the cell?

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.

GetWet wrote:

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

"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
  #33   Report Post  
Posted to microsoft.public.excel.misc
Joe Joe is offline
external usenet poster
 
Posts: 476
Default How do I merge two cells without deleting data from the cell?

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

  #34   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default How do I merge two cells without deleting data from the cell?

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?

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

  #36   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 420
Default How do I merge two cells without deleting data from the cell?

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:

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?

"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
  #37   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I merge two cells without deleting data from the cell?

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

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

  #38   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,819
Default How do I merge two cells without deleting data from the cell?

Then use this instead.

=A1&B1

Jess12 wrote:

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

"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 22nd 05 11:36 PM
moving data in excel without deleting existing data jigna Excel Discussion (Misc queries) 1 January 30th 05 11:35 AM
cells lose relativity to other WorkSheets after data sort? Ed Murray - ALPCO Excel Worksheet Functions 1 November 8th 04 07:02 PM


All times are GMT +1. The time now is 07:02 AM.

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

About Us

"It's about Microsoft Excel"