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

How do you counting characters in a string in Excel?

I found this to be a very useful little function. This is one of those formulas that is easy but most people may not know it so maybe you can impress your boss with it one day. The following formula will count the number of "B"s, both upper and lower case, in the string in B1.

=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(B1,"B",""),"b",""))