Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Combining text from several cells (Excel 2000)

    I'm a real beginner at Excel. Someone has given me a chart containing text entries, not numbers, and my job is to clean everything up. (The text in the chart was imported from several different sources, which ended up splitting up many of the entries that should all be in one cell.)

    I've attached a sample document showing you what I mean. The first row shows you how some of the entries in the document are now. The second row is the "cleaned-up" version of the first row.

    The only way I know to get all the text into the first cell is to double-click in the second cell, select the text, press Ctrl-C, double-click back in the first cell, paste onto the end of what's there, and then repeat the process for each cell in the row. Pretty cumbersome. Is there some way to select all the cells I want to combine and have everything put into the first cell in one fell swoop?

    I tried using the "Merge" option under Format Cells, but that deletes some of the text and also doubles the size of the cell.

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining text from several cells (Excel 2000)

    If all the columns are equally organised with data in say A, B & C
    Then something like

    =A1 & " " & B1 & " " & C1

    in Cell D1 would combine them.

    After that you can copy the D1 formula down the rows for as many as you need
    [editted to add this next bit]
    After that if you select the whole column, copy it, and "paste special > values" back onto itself you can then remove the original columns

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining text from several cells (Excel 2000)

    There are several possibilities here. First would be to use a formula. If the first cell is A1, and the last cell in the row with the most words is in column P, then you could enter a formula like the one below into a cell in an empty column and copy it down.

    <pre>=TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&P1)
    </pre>


    To eliminate the formulas and replace them with the actual text, do the following:

    1- Select the column with the formulas.

    2- Select Copy from the Edit menu.

    3- Select Paste Special from the Edit Menu

    4- In the resulting dialog box, select Values in the Paste section.

    5- Click OK.

    You should now have a column with the text you want, and you can select the columns containing the individual words and delete them.

    Another possibility would be to use a User Defined Function, like the one in <!post=This Post,397759>This Post<!/post> to concatenate the words.

    If you are going to have to do this often a fairly simple VBA macro would do everything automatically.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Combining text from several cells (Excel 2000)

    Wow, Andrew, that works great! It took me a while to try what you said and get it just right, but it really works. This will help a lot. Thanks so much.

    Russ

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    L.A., California, USA
    Posts
    267
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Combining text from several cells (Excel 2000)

    Thank you, Legare. That's just what I need.

    Russ

Posting Permissions

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