Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #31   Report Post  
Old January 29th 09, 07:50 PM posted to microsoft.public.excel.misc
MK MK is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 57
Default 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   Report Post  
Old January 29th 09, 11:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old February 4th 09, 01:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 1
Default 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   Report Post  
Old April 9th 09, 05:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 10
Default 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   Report Post  
Old August 28th 09, 09:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 1
Default 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   Report Post  
Old December 2nd 09, 07:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 1
Default 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.

  #37   Report Post  
Old December 2nd 09, 09:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #38   Report Post  
Old March 7th 10, 04:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 1
Default 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.

  #39   Report Post  
Old April 3rd 10, 07:19 PM posted to microsoft.public.excel.misc
Joe Joe is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 476
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.

  #40   Report Post  
Old May 10th 10, 05:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 2
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
Skipping cells while reorganizing data vertblancrouge Excel Discussion (Misc queries) 0 April 25th 05 07:26 PM
Deleting #N/A from cells... Jambruins Excel Discussion (Misc queries) 3 February 23rd 05 12:36 AM
moving data in excel without deleting existing data jigna Excel Discussion (Misc queries) 1 January 30th 05 12:35 PM
cells lose relativity to other WorkSheets after data sort? Ed Murray - ALPCO Excel Worksheet Functions 1 November 8th 04 08:02 PM


All times are GMT +1. The time now is 01:57 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017