1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Windows some excel trickery

Discussion in 'Software' started by bAnTAi, 23 May 2004.

  1. bAnTAi

    bAnTAi What's a Dremel?

    19 May 2002
    Likes Received:
    i have a list, in a .txt file, of names, about 500...they are listed first name then surname then a comma, then the next name


    John Smith, Paul Jones, Tony Blair, etc

    what i want to do is arrange them in excel in 2 columns with first name in one and surname in the other... like databases.

    Question: How? :thumb:
  2. ajack

    ajack rox

    17 Apr 2003
    Likes Received:
    In Excel:

    Data > Import External Data > Import Data.

    Navigate to the text file, then the Text Import Wizard will come up. Click Next on the first step. In the second step, check the "Comma" box the Next, then Finish then choose where you want the data to begin.
  3. SarverSystems

    SarverSystems What's a Dremel?

    28 Feb 2004
    Likes Received:
    Also ask this in the www.wopr.com forums. The folks there are EXTREMELY well versed in Office. Hell, they even wrote the books that we buy for Office!

    The problem is you wanted to make 3 columns of data out of 2 areas of data. You'll need soem kinf od formula to insert the 1st comma for you so that the comma seperator works properly.
  4. Deviate

    Deviate What's a Dremel?

    3 Jun 2002
    Likes Received:
    If I understand what you have it's like this:

    Mike Hunt, Hugh Jass, Amanda Hugginkiss,
    Tom Jones, Tony Randle, Frank Sinatra

    Is that right?

    You can open Excel and then choose File=>Open. Then for the file type choose Text Files. Then choose the .txt file you are interested in and click open. Then in the wizard choose the radio button for delimited, then next. Uncheck Tab and check Comma, then click finish. That will give you a complete name in each cell. In my example above you will have the following:

    Mike Hunt | Hugh Jass | Amanda Hugginkiss |
    Tom Jones | Tony Randle | Frank Sinatra |

    Forgive my crude drawing. :D What I think you want is 2 columns...one for first name and one for last name. What you need to do is manipulate the text file so that each name (first and last) is on it's own line and seperated by either a comma or a space. Then follow my steps above to get it to open in excel.

    HTH. If I just completely confused everyone, please let me know and I'll try to confuse you further. :hehe:
  5. demonstalker

    demonstalker What's a Dremel?

    24 Feb 2003
    Likes Received:
    Ok, first of all, to be clear its formated as follows
    <first name>(space)<last name>(space)(comma)(space)
    then it repeats right?
    Assuming that:
    Open the txt file in work, do a replace all...replace the (space)(comma)(space) with a linefeed. Then replace all the (space)'s with a tab. Then open in in excel..(the defaults should work, they normally are tab delimited) and you should be in business.

Share This Page