Remove ordinal date in Excel

Question

Is there a way to remove ordinal dates in Excel?

4-Feb-19
4/02/2019
4 02 2019
4 February 19
04 February 19
04 Feb 2019
4th Feb 2019
4th February 2019

All the dates above are fine and can be picked up with cell formatting apart from the last two. Is there away to format the cell so the ordinals (st, nd, rd, th etc) are removed?

Something like

DD?? MMM YYYY

Where ? denotes an ordinal letter (wildcard) to be removed. The alternative is to split the string, but that has its limitations just using formulas (which I'm limited to - macro free zone, here).

Answer 1

Scott suggested SUBSTITUTE, here's a possibility for you to try: The column needs to be text for this to work.

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd",""),"rd",""),"th",""))