Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#42
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
|
#43
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#44
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#45
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#46
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#47
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#48
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? . |
#49
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#50
|
|||
|
|||
You can also copy and paste the one cell content to another cell and then merge together or use the Wrap text feature.
|
#51
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Skipping cells while reorganizing data | Excel Discussion (Misc queries) | |||
Deleting #N/A from cells... | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) | |||
cells lose relativity to other WorkSheets after data sort? | Excel Worksheet Functions |