macro to get date from a cell of words

[tonywatsonhelp]

Hi Everyone,

In cell G28 i have this "robe UK AD Oct 2021.csv"

Now everything will always be in the same place, i want to get the date 01/10/2021 into G30 from the above data by pulling the year and the month out and using 01 for day always
totally stuck as i cant get vba or excel to recognise Oct or Nov or Dec as months?

please help if you can thanks
Tony

 

[Fluff]

Do you need a macro, you can just use this formula

=MID(G28,FIND("^",SUBSTITUTE(G28," ","^",3))+1,8)*1

 

[JGordon11]

or with G30 formatted as date you could use

=DATEVALUE(MID(G28,LEN(G28)-11,8))