score:1

So, using @Bathsheba suggestion, I went around this the long way but it works.

Try:

`=DATEVALUE(CONCATENATE(MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2),MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3),RIGHT(A1,4)))`

Explaination:

• `MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2)`-This finds the day in `dd` format from the string in `A1`

• `MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3)`-Takes the first three letters of the month from the string in `A1`

• `RIGHT(A1,4)`-Takes the year from the string in `A1`

It then takes these values and concatenates them into the format of `dd mmm yyyy`. The `DATEVALUE` then changes this into the correct format of `dd/mm/yyyy`. This works for all dates in you format.

Note: The cell, which this formula lies, should be formatted for short date.

score:1

Custom format the cells as `dd/mm/yyyy` and use this formula:

``````=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))
``````

or without any additional formatting:

``````=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"dd/mm/yyyy")
``````

score:1

then your dates might need a bit of cleaning :]

``````=Text( Mid( Trim(Clean(A1)), Find(" ", Trim(Clean(A1)) ), 99) * 1, "dd/mm/yyyy")
``````

Update

I just noticed the vba tag

``````[a1].NumberFormat = "dd/mm/yyyy"
[a1] = Split([a1], , 2)(1)        ' "Tuesday August 23, 2016" to "August 23, 2016"
``````