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? -- thebside ------------------------------------------------------------------------ thebside's Profile: http://www.excelforum.com/member.php...o&userid=22736 View this thread: http://www.excelforum.com/showthread...hreadid=476561 |
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" wrote in message ... 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 |
Parse out answers from a column
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 |
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" wrote in message news:Bryan.Hessey.1wz5ma_1129431903.3567@excelforu m-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 |
All times are GMT +1. The time now is 03:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com