Copy from many cells and paste to one

I need to copy a range of cells (C7:C20) and past the contents into one
cell (B4). the concatenate function is not the solution because I need
the results to appear as separate lines in the cell (B4) when pasted.
(leaving a "return" between lines)

Weird I know but Ahem, "It's for a friend".  ;)


Thanks,

Robert


-- 
eoreality
------------------------------------------------------------------------
eoreality's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24549
View this thread: http://www.excelforum.com/showthread.php?threadid=381448

0
6/22/2005 11:25:28 PM
excel 39879 articles. 2 followers. Follow

2 Replies
421 Views

Similar Articles

[PageSpeed] 23

J.E. McGimpsey has a User defined function that will concatenate cells.

You can find it at:
http://www.mcgimpsey.com/excel/udfs/multicat.html

You'll use it in the worksheet like:

=MultiCat(c7:c20,char(10))

J.E. shows this as his example:  =MultiCat(A1:C1," ")

Char(10) is that alt-enter that you want.

If you're new to macros, you may want to read David McRitchie's intro at: 
http://www.mvps.org/dmcritchie/excel/getstarted.htm

eoreality wrote:
> 
> I need to copy a range of cells (C7:C20) and past the contents into one
> cell (B4). the concatenate function is not the solution because I need
> the results to appear as separate lines in the cell (B4) when pasted.
> (leaving a "return" between lines)
> 
> Weird I know but Ahem, "It's for a friend".  ;)
> 
> Thanks,
> 
> Robert
> 
> --
> eoreality
> ------------------------------------------------------------------------
> eoreality's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24549
> View this thread: http://www.excelforum.com/showthread.php?threadid=381448

-- 

Dave Peterson
0
ec357201 (5290)
6/23/2005 12:30:49 AM
Robert

You could do this with a formula.

=C7 & CHAR(10) & C8 & CHAR(10) etc. until C20.

Or you could use a macro to do it.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
    On Error GoTo endit
    w = InputBox("Enter the Type of De-limiter Desired")
    Set z = Application.InputBox("Select Destination Cell", _
            "Destination Cell", , , , , , 8)
    Application.SendKeys "+{F8}"
    Set x = Application.InputBox _
    ("Select Cells...Contiguous or Non-Contiguous", _
            "Cells Selection", , , , , , 8)
    For Each y In x
        If Len(y.text) > 0 Then sbuf = sbuf & y.text & w & Chr(10)
    Next
    z = Left(sbuf, Len(sbuf) - 1)
    Exit Sub
endit:
    MsgBox "Nothing Selected.  Please try again."
End Sub

You probably don't need a delimiter in your case, so don't enter anything in
that inputbox.


Gord Dibben Excel MVP

On Wed, 22 Jun 2005 18:25:28 -0500, eoreality
<eoreality.1r1ynb_1119485165.7059@excelforum-nospam.com> wrote:

>
>I need to copy a range of cells (C7:C20) and past the contents into one
>cell (B4). the concatenate function is not the solution because I need
>the results to appear as separate lines in the cell (B4) when pasted.
>(leaving a "return" between lines)
>
>Weird I know but Ahem, "It's for a friend".  ;)
>
>
>Thanks,
>
>Robert

0
Gord
6/23/2005 12:45:54 AM
Reply:

Similar Artilces:

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Copy constructor
Dear people, I have a class C1 which contains a member variable of class C2. C2 didn't have a copy constructor up to now but due to a change needed (and got) one, and an assignment operator as well. Now when compiling, I get en error C2558: "class C1: No copy constructor available or copy constructor has been declared 'explicit'". It is correct that C1 doesn't have a copy constructor but I thought that the default copy constructor just calls the assignment operators of the members. Since C2 has an explicitly programmed assignment operator - where is the mi...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

copy does not work in word 2003
I can't seem to copy from word 2003. I select text but it won't take it. I can copy from an e-mail and paste into word, but word doesn't seem to want to copy, either from one word document to another or into my e-mail. It's been working fine for almost four years, it just acted up. Any suggestions? The usual reason is some third party add-in that is messing with the Office clipboard. If you start Word in Safe Mode, can you copy/paste normally then? From Start | Run (Winkey+R), type in winword /a and press Enter. Word will start in Safe Mode. Open two doc...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

Catch all outgoing email on Exchange and send copy to specified account
Hi all. I have been asked to find out if there is anyway to configure our mail server to catch all outgoing email from certain email accounts on our Exchange 2003 Enterprise server and forward a copy of this email to a specified account, also on the Exchange server. If anyone could point me in the right direction I'd be grateful. On 25 Oct 2006 04:10:54 -0700, googlegroups@tlbsolutions.com wrote: >Hi all. > >I have been asked to find out if there is anyway to configure our mail >server to catch all outgoing email from certain email accounts on our >Exchange 2003 Enterpr...

too many "inherited" custome headers and footers
It seems that somehow I have "inherited" custom headers and footers from someone else's workbook. They have filled up almost all the allocated spaces in the drop-down selection list and pre-empt me from adding more than two custom headers/footers of my own. I need to add at least four new custom headers to the list. Is there some way to delete all the "inherited" custom headers and footers? Thanks, JR ...

How to make a Add key disabled after adding one record unless
Hi, I have a form where I am adding records. There are two boxes where one has to put the lineitemnumber and another box where one has to put the description. Unless the above two boxes are filled in the Add command box need to be disabled. Now, the problem is after adding one record the Add button is enabled and I cannot disable it. I would appreciate any help to solve this. The error message is: The methos is not supported The following is the code: Private Sub cmdadd_Click() On Error GoTo Err_cmdadd_Click If (IsNull(Me.LineItemID) = True) Or (IsNull(Me.Description) = True) Then ...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

How do I print 4 postcards to one page to match my postcard paper
I bought perforated postcard paper that has 4 postcards per sheet. How do I create that in Publisher? I created it exactly how I want it to look and then realized it's only printing 2 per page. Thanks, Lauren Which version of Publisher are you using? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Lauren" <Lauren@discussions.microsoft.com> wrote in message news:DDA6778A-5DF0-4278-A1CA-896DC49FAFBA@microsoft.com... >I bought perforated postcard paper that has 4 postcards per sheet. How do >...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

How many pages can a booklet have?
Hi how many pages can a booklet have? Is there a limit to the pages in a publisher booklet. Each page has three photos. There is no limit, your RAM may decide for you. What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Verystumped" <Verystumped@discussions.microsoft.com> wrote in message news:8F2778CF-7B14-4BDD-93C5-EF9B39E2C1A7@microsoft.com... > Hi how many pages can a booklet have? Is there a limit to the pages in a > publisher booklet. Each page has three photos. ...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Excel 2002 converts 'S' to 0 when pasting from Clipboard
I came across the following problem: I copied some tabular data from IBM Personal Communications into the clipboard (yes, I am still a user of good old 3270 applications). Then I pasted the data into Microsoft Excel 2002 and all cells containing a 'S' became a '0' (number zero). Next I did some tests and found out that every single uppercase 'S' that is transferred to Excel using copy/paste is translated to '0'. This would not happen with other letters or with words containing an 'S'. Using 'Paste special' I can choose to insert my Clipboard a...