+ Reply to Thread
Results 1 to 4 of 4

Parse out answers from a column

  1. #1
    Registered User
    Join Date
    04-27-2005
    Posts
    5

    Parse out answers from a column

    Ok, I have a database that I am working on and in it there happens to be fields that have data in them that belong to two separate survey questions. I need to parse it so that the data in goes into the two separate columns.

    The data in the single column looks like this;
    Column 1 (What is your Job Title) Column 2 (What is your language Preference)
    Answer sets that start with "M" belong to Column 1, and answer sets that start with "L" belong to Column 2.
    In the single column that I Have now, the answers look like this "M1;M3;L02;L03;L08"

    Is it even possible to separate out the data into two columns?

  2. #2
    Tom Ogilvy
    Guest

    Re: Parse out answers from a column

    Untested, but use something like this

    Dim v as Variant, sStr1 as String
    Dim sStr2 as String, i as Long
    v = Split(ActiveCell.Value,";")
    for i = lbound(v) to ubound(v)
    if Left(v(i),1) = "M" then
    sStr1 = sStr1 & v(i) & ";"
    else
    sStr2 = sStr2 & v(i) & ";"
    end if
    Next
    if sStr1 <> "" then
    sStr1 = Left(sStr1,len(sStr1)-1)
    end if
    if sStr2 <> "" then
    sStr2 = Left(sStr2,len(sStr2)-1)
    end if

    ' now put sStr1 in Column 1 and sStr2 in Column 2

    works for xl2000 or later
    --
    Regards,
    Tom Ogilvy


    "thebside" <thebside.1wz02a_1129424704.8006@excelforum-nospam.com> wrote in
    message news:thebside.1wz02a_1129424704.8006@excelforum-nospam.com...
    >
    > Ok, I have a database that I am working on and in it there happens to be
    > fields that have data in them that belong to two separate survey
    > questions. I need to parse it so that the data in goes into the two
    > separate columns.
    >
    > The data in the single column looks like this;
    > Column 1 (What is your Job Title) Column 2 (What is your language
    > Preference)
    > Answer sets that start with "M" belong to Column 1, and answer sets
    > that start with "L" belong to Column 2.
    > In the single column that I Have now, the answers look like this
    > "M1;M3;L02;L03;L08"
    >
    > Is it even possible to separate out the data into two columns?
    >
    >
    > --
    > thebside
    > ------------------------------------------------------------------------
    > thebside's Profile:

    http://www.excelforum.com/member.php...o&userid=22736
    > View this thread: http://www.excelforum.com/showthread...hreadid=476561
    >




  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Hopefully you mean that your data is all within column A such that A1 is a heading, and A2=M1, A3=M3, A4=L02, A5=L03, A6=L08 etc

    Assuming that columns B and C are free, in B2 put
    =if(left(A2,1)="M",A2,"")
    and in C2 put either
    =if(B2="",A2,"")
    or
    =if(left(A2,1)="L",A2,"")

    formula-copy B2 and C2 to the end of your data in column A, this should show the required split, then

    highlilght columns B & C and COPY, then Paste Special, Values (back over themselves)

    You can then delete column A and adjust the headings.


    Quote Originally Posted by thebside
    Ok, I have a database that I am working on and in it there happens to be fields that have data in them that belong to two separate survey questions. I need to parse it so that the data in goes into the two separate columns.

    The data in the single column looks like this;
    Column 1 (What is your Job Title) Column 2 (What is your language Preference)
    Answer sets that start with "M" belong to Column 1, and answer sets that start with "L" belong to Column 2.
    In the single column that I Have now, the answers look like this "M1;M3;L02;L03;L08"

    Is it even possible to separate out the data into two columns?

  4. #4
    Gary Keramidas
    Guest

    Re: Parse out answers from a column

    nope, i think he means that "M1;M3;L02;L03;L08" is all in a2 or whatever
    row. tom's procedure seems to work nicely

    --


    Gary


    "Bryan Hessey" <Bryan.Hessey.1wz5ma_1129431903.3567@excelforum-nospam.com>
    wrote in message
    news:Bryan.Hessey.1wz5ma_1129431903.3567@excelforum-nospam.com...
    >
    > Hopefully you mean that your data is all within column A such that A1 is
    > a heading, and A2=M1, A3=M3, A4=L02, A5=L03, A6=L08 etc
    >
    > Assuming that columns B and C are free, in B2 put
    > =if(left(A2,1)="M",A2,"")
    > and in C2 put either
    > =if(B2="",A2,"")
    > or
    > =if(left(A2,1)="L",A2,"")
    >
    > formula-copy B2 and C2 to the end of your data in column A, this
    > should show the required split, then
    >
    > highlilght columns B & C and COPY, then Paste Special, Values (back
    > over themselves)
    >
    > You can then delete column A and adjust the headings.
    >
    >
    > thebside Wrote:
    >> Ok, I have a database that I am working on and in it there happens to be
    >> fields that have data in them that belong to two separate survey
    >> questions. I need to parse it so that the data in goes into the two
    >> separate columns.
    >>
    >> The data in the single column looks like this;
    >> Column 1 (What is your Job Title) Column 2 (What is your language
    >> Preference)
    >> Answer sets that start with "M" belong to Column 1, and answer sets
    >> that start with "L" belong to Column 2.
    >> In the single column that I Have now, the answers look like this
    >> "M1;M3;L02;L03;L08"
    >>
    >> Is it even possible to separate out the data into two columns?

    >
    >
    > --
    > Bryan Hessey
    > ------------------------------------------------------------------------
    > Bryan Hessey's Profile:
    > http://www.excelforum.com/member.php...o&userid=21059
    > View this thread: http://www.excelforum.com/showthread...hreadid=476561
    >




+ 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