+ Reply to Thread
Results 1 to 4 of 4

Exctracting comma seperated data from a single cell

  1. #1
    Registered User
    Join Date
    06-30-2006
    Posts
    8

    Cool Exctracting comma seperated data from a single cell

    I want to know how to autopopulate multiple cells from comma seperated data that is pasted into a single cell e.g. a csv file

    1,2,3

    Rob 1
    John 2
    Steve 3

  2. #2
    Franz Verga
    Guest

    Re: Exctracting comma seperated data from a single cell

    Nel post news:guyvanzyl.2a7h2o_1151655002.9794@excelforum-nospam.com
    *guyvanzyl* ha scritto:

    > I want to know how to autopopulate multiple cells from comma seperated
    > data that is pasted into a single cell e.g. a csv file
    >
    > 1,2,3
    >
    > Rob 1
    > John 2
    > Steve 3


    Excel is able to open csv file, so you should have no problem, but if you
    have, you have to way to solve:

    1) Before opening your file, change the extension of file to .txt and open
    it from Excel (menu File, Open, Files of type choose Text files, select your
    file, Text Import Wizard will start), and choose Delimited at first step of
    Text Import Wizard and Comma as Delimiters at step 2;
    2) With your file opened, use Text to Columns (Menu Data, Text to Columns)
    and choose Delimited at first step of Text to Columns Wizard and Comma as
    Delimiters at step 2.


    --
    (I'm not sure of names of menus, options and commands, because
    translating from the Italian version of Excel...)

    Hope I helped you.

    Thanks in advance for your feedback.

    Ciao

    Franz Verga from Italy



  3. #3
    Registered User
    Join Date
    06-30-2006
    Posts
    8

    Re: Exctracting comma seperated data from a single cell

    Thanks Franz, I will give it a go. I have used the text to columns function but it auto populates the data across 3 columns rather than in selected cells. Here is a layout that probably makes more sense

    A B
    1,2,3,4

    A 1
    B 2
    C 3
    D 4

    I want the values in A1 (1,2,3,4) to auto populate into B3, B4, B5,B6
    Last edited by guyvanzyl; 06-30-2006 at 06:18 AM.

  4. #4
    CLR
    Guest

    RE: Exctracting comma seperated data from a single cell

    With your string of 1,2,3 in A1, use these formulas

    In B1 =LEFT(A1,FIND(",",A1,1)-1) returns 1
    In B2 =MID(A1,FIND(",",A1,1)+1,FIND(",",A1,FIND(",",A1,1))-1) returns 2
    IN B3 =MID(A1,FIND(",",A1,FIND(",",A1,1)+1)+1,99) returns 3

    hth
    Vaya con Dios,
    Chuck, CABGx3



    "guyvanzyl" wrote:

    >
    > I want to know how to autopopulate multiple cells from comma seperated
    > data that is pasted into a single cell e.g. a csv file
    >
    > 1,2,3
    >
    > Rob 1
    > John 2
    > Steve 3
    >
    >
    > --
    > guyvanzyl
    > ------------------------------------------------------------------------
    > guyvanzyl's Profile: http://www.excelforum.com/member.php...o&userid=35927
    > View this thread: http://www.excelforum.com/showthread...hreadid=557212
    >
    >


+ 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