Excel question v.screenshots

Discussion in 'OT Technology' started by Falconer, Feb 14, 2010.

  1. Falconer

    Falconer OT Supporter

    Joined:
    Jun 23, 2006
    Messages:
    65,506
    Likes Received:
    1
    I'm sure there's an easy way to do this, I'm just not thinking of it.

    I need to generate a large number of random names and then copy them into a single column in another file.

    I have an excel file with all the first names in one column and all the last names in the next column. Each column has a column of =rand() next to it. When I need to generate a new list of names, I just sort each column by their =rand() column. So it looks like this:

    [​IMG]

    I can then sort, and then highlight columns B and C and copy them and get my list of randomly matched first and last names.

    The problem is that I then have to paste these names into a new column in another Excel spreadsheet, but when I do, I need the first and last names together in the same cell, so that it only spans one column rather than two.

    So obviously when I have them copied, if I paste them how they are they will paste into two columns.

    So I thought that I could first paste them into Notepad and then that would strip all the formatting from them, and then I could copy them again from notepad and paste them into Excel and they would all appear in one column, but that didn't work. The column formatting apparently carries into Notepad and then when I copy and paste them from Notepad into Excel, they still go into two columns.

    I tried highlighting one column first and pasting them that way, thinking maybe Excel would "force" them into one column since I had one column selected, but that didn't work, either.

    So how do I do this?

    I don't want to do a cell merge because that just makes it look like one column rather than actually being in one column.

    In other words, I need them to look like this:

    [​IMG]

    Rather than this:

    [​IMG]

    Thanks.
     
  2. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    = B1 & " " & C1


    then copy that.. string joining
     
  3. shiba

    shiba Active Member

    Joined:
    Feb 10, 2006
    Messages:
    1,716
    Likes Received:
    0
    or concatonate function.
     
  4. Falconer

    Falconer OT Supporter

    Joined:
    Jun 23, 2006
    Messages:
    65,506
    Likes Received:
    1
    With the concatonate function I was only able to put them together without a space in the middle (AndrewWillie, JustinKirkland) and also only in one row at a time. Am I missing something?
     
  5. Falconer

    Falconer OT Supporter

    Joined:
    Jun 23, 2006
    Messages:
    65,506
    Likes Received:
    1
    Cool, that worked :h5:
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yes, the added space:

    =B1 & " " & C1

    is the same as:

    =concatenate(b1," ",c1)
     

Share This Page