Remove weekday from string, creating date from output-CodePudding

I'm working on a sheet with lots of dates which are currently entered as weekday day month (i.e. 'Friday 30th September'). I'm looking to convert these to actual date entries (dd/mm/yyyy) whilst still preserving the original cells.

I've attempted to do this by using RIGHT & CONCAT to put the two strings together:

=CONCAT(RIGHT(B822,15)," 2022")

Whilst this does create the correct output, these are still strings rather than dates - and doesn't work with a query that I'm running from another sheet, which is selecting columns based on whether the date falls within the next seven days.

Using the above formula, it would also require amending if there was no leading 0 before the date, when the next month begins which adds further complications.

Any help would be greatly appreciated.

CodePudding user response：

use:

=REGEXREPLACE(A1; "(.*) (\d ).  (.*)"; "$2$3")*1

=INDEX(IFERROR(1/(1/REGEXREPLACE(A1:A3; "(.*) (\d ).  (.*)"; "$2$3"))))