Split cell into 4 seperate cols .

[Sunline]

Hello all , i was wanting to know if there are some functions that can get data from col D and put this into cols C E F and G .

The first 4 rows i have given example , i will always fill cols A and B myself .

Most of the data in from the right side of cell but because they are all different lenghts this makes it tricky using functions left , mid , right etc .

Note cell F2 is blank because there isnt a class mentioned in cell D2 .
Thanks .

Excel Workbook
	A	B	C	D	E	F	G
1	Mon	Year	Date	Type	G123L	Class	Dist
2	Apr	1989	1/04/1989	WFA G1, G1, WFA 2000m - April 1	1	WFA	2000
3	Apr	1989	1/04/1989	G3 RACE IMAGES STAKES, G3, 1600m - April 1	3		1600
4	Apr	1989	8/04/1989	81ST SWETTENHAM STUD SIRES PRODUCE STAKES, G1, 2YO 1400m - April 8	1	2YO	1400
5	Apr	1989	15/04/1989	WYBORN STAKES, G2, F&M 1600m - April 15	2	F&M	1600
6	Apr	1989		G3 TERRACE REGENCY HOTEL WEIGHT-FOR-AGE, G3, WFA 1400m - April 19
7	Apr	1989		18TH OWENS INTERNATIONAL, G3, 1600m - April 22
8	Apr	1989		HAWKES BAY CUP, G3, 2200m - April 22
9	Apr	1989		G3 REDOUBT CLASSIC, G3, 2YOF HCP 1200m - April 25
10	Apr	1989		STAR WAY 2000, L, WFA F&M 2000m - April 25
11	Apr	1989		3YO, L, 3YO HCP 1600m - April 25
Sheet1

 

[RonB1111]

C2: =datevalue(a2&b2)+right(d2,2)-1
e2: =if(mid(d2,search(",",d2)+3,1)=",","",mid(d2,search(",",d2)+3,1))
f2: =if(mid(d2,search("-",d2)-8,1)=",","",mid(d2,search("-",d2)-10,3))
g2: =mid(d2,search("-",d2)-6,4)

 

[Sunline]

Thanks RonB1111 .
The col C is working fine .
Col E is working but not recognising letter L .
Col F is working some as well but cell F6 now says TEL , should be WFA , cells F9 and F11 now say HCP should say 2YOF HCP AND 3YO HCP .
Col G cell G6 now says WEIG should be 1400 .

Overall its a big help and less cutting and pasting . Thanks .

 

[bosco_yip]

1] Column C
=--(TRIM(RIGHT(D2,2))&A2&B2)

2] Column E
=MID(D2,FIND("&",SUBSTITUTE(D2,",","&",2))-1,1)

3] Column F
=IFERROR(MID(D2,FIND("&",SUBSTITUTE(D2,",","&",2))+2,FIND("m -",D2)-FIND("&",SUBSTITUTE(D2,",","&",2))-7),"")

4] Column G
=MID(D2,SEARCH("m -",D2)-4,4)

 

[Sunline]

Thanks bosco_yip , they are working great , thankyou .