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. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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. |
#32
|
|||
|
|||
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. |
#33
|
|||
|
|||
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. |
#34
|
|||
|
|||
How do I combine data in cells A1=Prog, B1=02-04-2009 in cell C1?
"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. |
#35
|
|||
|
|||
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. |
#36
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
How do you merge two calls when one row contains the same information and the
second row contains different information "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. |
#37
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
Please read again what Peo told you.
-- David Biddulph "bonnie" wrote in message ... How do you merge two calls when one row contains the same information and the second row contains different information "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
|
|||
|
|||
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. |
#39
|
|||
|
|||
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 |
#40
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
wow! many thanks... it worked perfectly fine and solved my problem. Best
Regards, Wahaj "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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
multiple docs, one data source | kp | Mailmerge | 12 | January 31st, 2005 04:41 PM |
Pulling data from 1 sheet to another | Dave1155 | Worksheet Functions | 1 | January 12th, 2005 05: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 |