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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
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 |