+ Reply to Thread
Results 1 to 15 of 15

combining cells into one, but seperated by carriage return

  1. #1
    Registered User
    Join Date
    10-04-2006
    Posts
    20

    combining cells into one, but seperated by carriage return

    I have rows of data in one column. I want to combine them all into one cell and have each row on a new line.

    I've tried =A1 & char(10) & A2, (and a bunch of other char(#s) for carriage return) but it just shows an open box in place of the char().

    I hope I can do this w/o VB. Help?

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Try formatting the cell as wrap text

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    No, I do not want to wrap the text. I'd like each piece of data on a different row within the cell (similar to pressing alt+enter in the cell). Sorry for the confusion.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Format as wrap then Paste special values and see

    VBA Noob

  5. #5
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    But how would I get all data into one cell?

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    What do you mean all data.

    Are you concatenating more than two cells then ??

    VBA Noob

  7. #7
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    Yes, it is a long list. I want to find a non-manual way of combining them into one cell.

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Try some code. Link below explains how to add

    Save your data first then Select column to concatenate and run macro

    Please Login or Register  to view this content.
    VBA Noob

  9. #9
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    Sorry I'm more of a noob than you VBA Noob ... is there anyway to do this w/o VB? When I have more time I will try this out. Need to read some how-to's to refresh my memory on these things. Thanks for your help though.

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    This link explains more

    http://www.mcgimpsey.com/excel/modules.html

    Plus here's an example. Select cells in col A first

    VBA Noob
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    Thanks your sample file really helps!!

    But the only thing is, the Destination Cell includes a weird open box character at the end of each line. I'm not sure how to find/replace this. Is there something I can change in the VB code to fix this?

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Can you post an example

    What font are you using

    What version of excel

    VBA Noob

  13. #13
    Registered User
    Join Date
    10-04-2006
    Posts
    20
    I'm using Excel 2003 (11.8012.6568) SP2

    Not sure if the attachment will help since it is the same file you uploaded. So you are not seeing any weird characters in your destination cells? If I copy and paste the raw data (by double-clicking the cell first and highlighting the text) I get 2 carriage returns after each line instead of just one (but the box characters disappear).

    Oh sorry, using Arial font

    Edit: added screenshot; cell C8 is where I pasted the "raw" data by double-clicking the cell before pasting.
    Attached Files Attached Files
    Last edited by sysco; 02-09-2007 at 02:40 PM. Reason: adding screenshot

  14. #14
    Registered User
    Join Date
    11-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: combining cells into one, but seperated by carriage return

    Hi,
    I have a database file with list of problems and action details for each problem.
    The sample database and Requirements are shown below :
    Sample.jpg

    Excel File attached for ref.

    Please help me with a Suitable Macro.Sample.xls

  15. #15
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: combining cells into one, but seperated by carriage return

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1