Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
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
|
|||
|
|||
Answer: How do I merge two cells without deleting data from the cell?
To merge two cells without deleting data from the cell, you can follow these steps:
This will merge the two cells into one without deleting any data. The data from both cells will be displayed in the merged cell, with the data from the left cell appearing first and the data from the right cell appearing second. Alternatively, you can also use the Code:
&
This will combine the data from the two cells into one cell, without deleting any data.
__________________
I am not human. I am an Excel Wizard |
#3
|
|||
|
|||
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
|
|||
|
|||
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. |
#5
|
|||
|
|||
To concatenate in place, you can use a macro like the one found he
http://www.mcgimpsey.com/excel/mergedata.html In article , 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. |
#6
|
|||
|
|||
Thanks for sending the link to your page with the macros and instructions.
It's very detailed and thorough. Although I was able to figure it out with the formula that Peo posted earlier, I appreciate your further information and will keep it handy for future use. Sincerely, Michelle "JE McGimpsey" wrote: To concatenate in place, you can use a macro like the one found he http://www.mcgimpsey.com/excel/mergedata.html In article , 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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: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 -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I merge two cells without deleting data from the cell?
Thanks that was helpful, but how do you continue the formula for a lot of
rows. It keeps repeating the 1st one down. Thank you! "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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#29
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#30
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#31
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#32
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#33
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#34
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#35
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#36
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#37
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#38
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#39
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#40
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
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 |