A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #41  
Old April 3rd, 2010, 07:19 PM posted to microsoft.public.excel.misc
Joe
external usenet poster
 
Posts: 1,218
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.

  #42  
Old May 10th, 2010, 05:58 PM posted to microsoft.public.excel.misc
Stephie
external usenet poster
 
Posts: 4
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.

  #44  
Old May 10th, 2010, 07:02 PM posted to microsoft.public.excel.misc
Dave Peterson[_2_]
external usenet poster
 
Posts: 69
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
  #45  
Old May 27th, 2010, 09:46 PM posted to microsoft.public.excel.misc
Jess12[_2_]
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.

  #46  
Old June 1st, 2010, 07:02 PM posted to microsoft.public.excel.misc
Bob I
external usenet poster
 
Posts: 10,698
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.


  #47  
Old June 2nd, 2010, 07:15 PM posted to microsoft.public.excel.misc
GonzaloRC
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?



  #48  
Old June 2nd, 2010, 07:30 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
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?




  #49  
Old June 2nd, 2010, 08:19 PM posted to microsoft.public.excel.misc
GonzaloRC
external usenet poster
 
Posts: 2
Default How do I merge two cells without deleting data from the cell?

Thanks Gord...

"Gord Dibben" wrote:

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?



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple docs, one data source kp Mailmerge 12 January 31st, 2005 05:41 PM
Pulling data from 1 sheet to another Dave1155 Worksheet Functions 1 January 12th, 2005 06:55 PM
How do I get 3 series in sync with the x-axis? zizbird Charts and Charting 10 October 25th, 2004 01:23 PM
Is this possible with Excel Chart? q582gmzhi Charts and Charting 1 September 8th, 2004 03:33 AM
How to run word and pass a mail merge values and fax it to the recipient Belinda Mailmerge 2 June 13th, 2004 12:49 AM


All times are GMT +1. The time now is 12:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.