how can i convert Saturday, January 3, 1970 into 03/01/1970 into the same or separate cell?

[arniebun]

Hi I am trying to convert Thursday, January 3, 1970 from one cell to the next showing 03/01/1970, or be able to replace the original cell with this date formatting. I am trying to extract data off a website but its going to be loads so will take me hours of flicking keys that would drive me insane. their are a number of different dates and months so it would have to recognised the month year day (3) and discard Saturday or put it into a different cell because would be good to see relationships on the days of the week. would really appropriate your help with this.

 

[Dave Patton]

N.B.
- my Regional Settings for date are dd-mm-yy USA centric systems reverse coercion of Month
- format the date for your preference. I formatted the date to include the day of the week.
- the formula is much more concise if you use the helper cell
- if you use the helper cell, ignore C2.

T202006b.xlsm
	A	B	C
2	Thursday, January 3, 1970		Sat 03-Jan-1970
3	Thursday, January 3, 1970	January 3 1970	Sat 03-Jan-1970
4	Monday, June 29, 2020	June 29 2020	Mon 29-Jun-2020
5
3c

Cell Formulas
Range	Formula
C2	C2	=DATE(RIGHT(MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99),4),MONTH(1&LEFT(MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99),3)),MID(B3,FIND(" ",MID(SUBSTITUTE(A2,",",""),FIND(" ",SUBSTITUTE(A2,",",""))+1,99))+1,2))
B3:B4	B3	=MID(SUBSTITUTE(A3,",",""),FIND(" ",SUBSTITUTE(A3,",",""))+1,99)
C3:C4	C3	=DATE(RIGHT(B3,4),MONTH(1&LEFT(B3,3)),MID(B3,FIND(" ",B3)+1,2))

 

[Fluff]

Another option
=DATE(RIGHT(A2,4),MONTH(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),100,100))),SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,100)),",",""))

 

[arniebun]

brilliant thanks a mill, really has saved me some time sifting though 50 years of data

 

[Fluff]

Glad we could help & thanks for the feedback.