Formula to extract data according to dates

[yudadude]

Need a solution, Need to sort local election results, I have one column with the voting history dates for each registered voter for several years, I’m trying break up the voting dates to able to see which voter voted in which election/year, each date is separated with a semicolon in the same cell, see below example,Row 1 has, 20161108 GE;20131105 GE;20130910 PR;20121106 GE;20101102 GERow 2 has,20170912 PR;20161108 GE;20160913 PR;20141104 GE;20131105 GE;20130910 PR;Row 3 has,20161108 GE;20120320 SP;20081104 GE;General Election 2005;General Election 2004I’m looking for a formula that will do text to column and leave blank cells for the missing years, so that column B will have all the 2017 dates column C will have all 2016 dates, so that column D will have all the 2015 dates.note: date are left to right, high to lowPlease let me know if you have a solution for me,thank you very much

 

[frank_AL]

Excel has a built in tool on the Data Tab called Text to Columns. That function will separate your data into individual columns much like importing a CSV delimited file.
However, that function cannot give you the second part of your request which is to put the data in specific columns based on year.
There are many experts on this forum that do amazing things with existing Excel functions that may be able to solution that part of your request.

For me, I could provide a solution via a VBA code module.

 

[István Hirsch]

Try this. First enter the numbers in bold (years) in row 1. Then enter the formula in C2, then draw it down and across. Data in 2005 and before should be conversed before this processing. It is assumed that there are only two elections in the same year.

Excel Workbook
	A	B	C	D	E	F	G	H
1	NAME		2017	2016	2015	2014	2013	2012
2	x.Y.	20161108 GE;20131105 GE;20130910 PR;20121106 GE;20101102 GE;		20161108 GE;			20131105 GE;20130910 PR;	20121106 GE;
3	Z.Q.	20170912 PR;20161108 GE;20160913 PR;20141104 GE;20131105 GE;20130910 PR;20170912 PR;	20170912 PR;20170912 PR;	20161108 GE;20160913 PR;		20141104 GE;20141104 GE;	20131105 GE;20130910 PR;
4	P:S:	20161108 GE;20120320 SP;20081104 GE;General Election 2005;General Election 2004;		20161108 GE;				20120320 SP;20120320 SP;
5	Y.L.	20170912 PR;20161108 GE;20160913 PR;20141104 GE;20131105 GE;20130910 PR	20170912 PR;	20161108 GE;20160913 PR;		20141104 GE;20141104 GE;	20131105 GE;20131105 GE;
Sheet1