1. You are viewing our forum as a guest. For full access please Register. WindowsBBS.com is completely free, paid for by advertisers and donations.

EXCEL, convert 1 column to two column

Discussion in 'Other PC Software' started by OLDSALTY, 2006/01/16.

  1. 2006/01/16
    OLDSALTY Lifetime Subscription

    OLDSALTY Inactive Thread Starter

    Joined:
    2003/02/21
    Messages:
    573
    Likes Received:
    0
    Office 2003, Excel
    I have one column with first name and last name, I want to convert this to two columns one with first name and the other last name.
     
    OLDSALTY,
    #1
  2. 2006/01/16
    Welshjim

    Welshjim Inactive

    Joined:
    2002/01/07
    Messages:
    5,643
    Likes Received:
    0
    OLD SALTY--I suggest you go into one or the other of these duplicate posts,
    http://www.windowsbbs.com/showthread.php?t=51070
    click the Edit button, wipe out the message and perhaps replace with OOPS, or Duplicate Post. That way all the answers will be collected in one thread. :)
    I think you can also change the title to Duplicate Post or Test, etc.
     
    Welshjim,
    #2


  3. to hide this advert.

  4. 2006/01/16
    PeteC

    PeteC SuperGeek Staff

    Joined:
    2002/05/10
    Messages:
    28,896
    Likes Received:
    389
    Dupe deleted.

    Old Salty - guess the only way you will achieve that is to create a new column and cut and paste either the first name or last name across to the new column - unless someone else knows better :)
     
    PeteC,
    #3
  5. 2006/01/16
    Christer

    Christer Geek Member Staff

    Joined:
    2002/12/17
    Messages:
    6,585
    Likes Received:
    74
    A bit of work but it does the trick:

    Copy the cells in the column (not the whole column) to a new excel document.

    Edit > Replace: "a space" with "a comma" (Old Salty becomes Old,Salty).

    Save as: *.csv

    When you close the excel document, do not save a second time.

    Open Excel and open the file you just saved as *.csv. (In the open dialog, you have to select "all files ".) The import dialog will ask for the "separator" which is "comma ". Follow the prompts and finish the import.

    Now, you should have two columns to copy and paste into the original document.

    Christer
     
    Last edited: 2006/01/16
    Christer,
    #4
  6. 2006/01/16
    OLDSALTY Lifetime Subscription

    OLDSALTY Inactive Thread Starter

    Joined:
    2003/02/21
    Messages:
    573
    Likes Received:
    0
    Jim I don't understand. The link goes nowhere.
     
    OLDSALTY,
    #5
  7. 2006/01/16
    OLDSALTY Lifetime Subscription

    OLDSALTY Inactive Thread Starter

    Joined:
    2003/02/21
    Messages:
    573
    Likes Received:
    0
    I got the answer after a lot of reading in help. It seems I have to select the column and then Data on the menu and then text to columns. I tried it and I have what I wanted. This is a neet thing.
    Thanks to all.
     
    OLDSALTY,
    #6
  8. 2006/01/16
    Christer

    Christer Geek Member Staff

    Joined:
    2002/12/17
    Messages:
    6,585
    Likes Received:
    74
    Nice and simple and a good find!

    I import a lot of text files (data in lines) into Excel and my method is time consuming. I will try to import the whole text into a single column and do "text to columns" instead.

    Christer
     
    Christer,
    #7
  9. 2006/01/16
    Royalty

    Royalty Inactive

    Joined:
    2004/10/31
    Messages:
    25
    Likes Received:
    0
    Text to columns DOES work great, but you need to have a delimiter that can be recognized, or use a fixed length (if the text you are separating is the same length in each record. If you opt to use a delimiter it can be just about any character, "space ", "tab ", "symbol "(?, %, !, $, etc). You can also parse the text into more than two columns, as long as it runs into a delimiter or enough characters for the fixed length option. You can optionally elect to overwrite data already in the columns to the right (where you want the parsed out text to end up), or just insert enough blank columns for the text you are parsing out.

    Another handy thing about this function is that you can define each new column you create from this function as text or general, which in effect, will allow you to convert numbers into text and/or text into numbers for a whole column without having to recalculate each cell.
     
    Royalty,
    #8

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...