How to remove ordinal abbreviations from dates and numbers

Did you know there is a term for the two letters that follow the number of a date? You know, the "st" in 1st; the "nd" in 2nd; the "rd" in 3rd; the "th" in 4th? They're called ordinal abbreviations. These ordinal abbreviations are actually hybrid contractions of a numeral and a word. 1st is "1" + "st" from "first". Similarly, "nd" is used for "second" and "rd" for "third". Sometimes they're also referred to as elevated terminals due to the way they're typically written: 1st, 2nd, 3rd, 4th, etc..

What does this have to do with Excel?

Recently, a reader asked me a question. He was the the date in column B written as 11th, 1st, 2nd, etc., the month written out in column C, and the year in column D. So the question was how to transform these three separate columns: 11th December 219 into this one single column in Excel: 11/12/2019.

Here's how to solve this seemingly complex problem: break down into smaller problems.

First, to combine multiple columns into one, use concatenate. =b2&"/"&c2&"/"&d2.

Next, to change a month written out to a number, use this formula: =MONTH(1&C2)

Third, use SUBSTITUTE to find the ordinal abbreviations and replace them with nothing.
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"st",""),"nd",""),"rd",""),"th","")

Finally, put all the elements together for one long formula that seems complicated but is quite simple when you break it down into parts:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"st",""),"nd",""),"rd",""),"th","")&"/"&MONTH(1&C2)&"/"&D2