+ Reply to Thread
Results 1 to 14 of 14

Help with Merging Cells

  1. #1
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Help with Merging Cells

    Hello,

    I have a document that I need help with.

    The document has 10 different columns, and I need all the data in just 1 column, not 10 different columns. Does anyone know an easy way to do this? I tried to highlight the row and merge, but it did not work.

    Does anyone know an easy way to move all the items into one column?

    Example: Row 8 would have: * Corporate, Estate Planning/Wills, Business Law,Probate & Trust, Tax-Federal*in all 1 cell, not different cells.


    Thanks for your help!
    Kimmy
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-28-2009
    Location
    Silicon Valley, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Help with Merging Cells

    You can try combining the values in one cell with the following formula.

    Say cell B1 says Mary and cell C1 says Smith. In another cell you can type

    =B1&" "&C1

    to get "Mary Smith" in that cell.

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Help with Merging Cells

    Example: Row 8 would have: * Corporate, Estate Planning/Wills, Business Law,Probate & Trust, Tax-Federal*in all 1 cell, not different cells.
    Did you mean to include the * character? (omitted below)

    The formula would be:
    =A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8

    Do you really need the comma? Reason I ask is because you will have trailing commas for cells that are empty.

    Also, the cells that appear blank are, in fact, not blank. Try a simple COUNTA or COUNTBLANK on a row and you will see it returns a value greater than cells which contain "visible" text.

  4. #4
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Merging Cells

    Thank you all so much for the feedback, this is wonderful.

    It seems to be working, however, I do need the comma in between the words. If I use the formula: =A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8, how do I get the comma?

    Thanks again! This is going to save me SO much time!
    Kimmy

  5. #5
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Merging Cells

    I also forgot to ask:

    I have to do this for about 2300 cells. Do I just keep changing the line name?

    Thanks!

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Help with Merging Cells

    It seems to be working, however, I do need the comma in between the words. If I use the formula: =A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8, how do I get the comma?
    Instead of " "between each reference, use ", ". IF you drag the formula down the column, the cells will automatically advance for each new row.

    Now if you want to remove the formula and have it as text, you need to copy the column (with the formula) and Paste Special >Value. That will convert the formulas to text. Hope this helped.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Help with Merging Cells

    how do I get the comma?
    Comma added to formula.
    =B8& "," &C8&","&D8&","&E8&","&F8&","&G8&","&H8&","&I8&","&J8&","&K8

    I have to do this for about 2300 cells. Do I just keep changing the line name?
    Because you may have empty cells in column-J, I would do the following to make it fast and easy.

    Click cell A1, then right-click and choose Insert Column.

    Put the formula into cell A8.
    With A8 the active cell, double-click the fill-handle of the cell pointer. The fill-handle is the tiny square at the lower right of the cell pointer.

    Excel will automatically fill down column-A as many rows as are filled in the adjacent column (B).

    For the rows above A8, just drag the fill-hand upward to fill those cells with the formula).
    Last edited by Palmetto; 08-28-2009 at 04:02 PM.

  8. #8
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Merging Cells

    I seem to be receiving a Circular Reference Warning error message after I plug in the formula.



    Any suggestions? I am running Excel 2007.

  9. #9
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Merging Cells

    I also do not need any spaces in between the words. Example:
    Corporate,Real Estate,Tax-Federal

    We are in the long process of updating databases and are currently working with a company to import our data.

    Thanks!!!

  10. #10
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Help with Merging Cells

    I seem to be receiving a Circular Reference Warning error message after I plug in the formula.
    I forgot to adjust the column references to account for inserting a new column. The formula in my previous post is corrected.

    I also do not need any spaces in between the words.
    Formula amended and spaces removed.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Help with Merging Cells

    I also do not need any spaces in between the words. Example:
    Corporate,Real Estate,Tax-Federal
    Then Palmetto's formula (with ",") should work.

    If you are getting a circular reference, that means that your formula is referring to the cell where you are putting the formula. For example, with your example, the formula cannot go into B8, C8,D8,E8,F8,G8,H8,I8,J8 or K8. You need to put it into either A8, L8, M8 etc.

  12. #12
    Registered User
    Join Date
    08-05-2009
    Location
    Fairfax, VA
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Help with Merging Cells

    This is absolutely wonderful! It is working, however, I still have spaces between my comma's:

    Corporate ,  Estate Planning/Wills ,  Business Law ,  Probate & Trust ,  Tax-Federal 

    Is there anyway to remove the spaces without having to go through the document one by one?

  13. #13
    Registered User
    Join Date
    08-28-2009
    Location
    Silicon Valley, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Help with Merging Cells

    The first thing I would check is to see if there are spaces between the quotes in the formula. If there were no spaces there, I would then check to see if there were spaces inside the referenced cells (where the data is). So I would check the cell that said "Corporate" by clicking inside it to see if it actually said " corporate " with spaces before and/or after the visible letters.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Help with Merging Cells

    The cells in your grid contain a few instances of CHAR 160, might be an idea to highlight the entire matrix and run an Edit -> Replace, in Replace With box type 0160 (numeric keypad) whilst holding ALT (if using a laptop hold ALT + FN and type 0160), in Replace With leave blank.

    You could then in theory use a slight variant of the earlier code to generate the string

    Please Login or Register  to view this content.

+ 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