If you’re not familiar with the term ordinal, think 1st, 2nd, 3rd, 500th, etc
To generate a sequential list of ordinals in Excel you can use Autofill…
- Type the first required ordinal into a cell with or without additional text, for example 3rd Round
- Point your mouse at the bottom right hand corner of that cell so the mouse pointer becomes a small black cross
- Hold your left mouse button down and drag down or across as many cells as required.
- The result…
But what if you need to generate a single ordinal from a single numeric value in a cell? In my example I enter a number such as 12 into B1 and the formula in B3 displays 12th. In that situation you need a more complex formula. This is the formula that I used in the video:
=B1 & IF(OR(MOD(B1,100) = 11, MOD(B1,100) = 12, MOD(B1,100) = 13), "th", IF(MOD(B1,10) = 1, "st", IF(MOD(B1,10) = 2, "nd", IF(MOD(B1,10) = 3, "rd", "th"))))
The first part of the formula (before the &) simply takes the value that is in B1
The rest of the formula works out whether to append st, nd, rd or th to the number….
- Anything that ends in 1 such as 1, 21, 31, 101, 121 has st appended to it: 1st, 21st, 31st, 101st, 121st.
- Anything that ends in 2 such as 2, 22, 32, 102, 122 has nd appended to it: 2nd, 22nd, 32nd, 102nd, 122nd
- Anything that ends in 3 such as 3, 23, 33, 103, 123 has rd appended to it: 3rd, 23rd, 33rd, 103rd, 123rd
- Anything else has th appended to it: 4th, 10th, 27th, 105th, 289th, etc
But there are exceptions to that rule and those exceptions are 11, 12 and 13 which end in 1, 2 and 3 but the ordinal is always th (i.e. 11th, 12th, 13th)
The next part of the formula checks for those exceptions. We need to know if the value in B1 is 11 or 12 or 13. But it’s not just 11, 12 or 13. It could be 111, 112, 113 or 211, 212, 213 or 311, 312, 313 etc etc
So this is the formula I’ve used…
IF(OR(MOD(B1,100) = 11, MOD(B1,100) = 12, MOD(B1,100) = 13), “th”
The MOD function takes a number – in this case the number in B1
and divides it by the number that is the second argument – in this case 100
And the result is the remainder. Without getting too mathematical…
Suppose B1 contains 212. We don’t want the ordinal to be 212nd (remember, anything that ends in 2 normally has nd appended to the end) so we’d say 212 divided by 100 is 2 remainder 12. In other words there are 2 100’s in 212 which added together gives you 200 and that leaves 12.
So the result of MOD(B1,100) is 12
So this part of the formula is saying if the result of the MOD(B1,100) formula is 11 or 12 or 13 then append th to the number in B1
Now, what if the number doesn’t end in 11, 12, or 13?
- If it ends in 1 – as long as it’s not 11 – I want st to be appended (1st, 21st, 551st etc)
- If it ends in 2 – as long as it’s not 12 – I want nd to be appended (2nd, 22nd, 552nd etc)
- If it ends in 3 – as long as it’s not 13 – I want rd to be appended (3rd, 23rd, 553rd etc)
- If it ends in any other number – 4,5,6,7,8,9 or 0… I want th to be appended (4th, 16th, 29th, 570th etc)
So this is the formula that I used:
IF(MOD(B1,10) = 1, “st”, IF(MOD(B1,10) = 2, “nd”, IF(MOD(B1,10) = 3, “rd”, “th”
This formula takes the number in B1, divides it by 10 and the answer is the remainder. So if B1 contains 32, there are 3 10’s in 32. 3 10’s are 30. 32 minus 30 is 2. 2 has nd appended to it so we’d get 32nd – which is correct
By the way if you’re wondering when to use 100 with the MOD function and when to use 10…when you want to check the last two digits (exceptions like 11, 12, 13, or 211, 212, 213) use 100 but when you want to check the last digit only (the majority of cases) – use 10
And that’s it. Not as easy as you think. Thanks for sticking with me through the maths lesson. Now go and watch the video!
Download a copy of the file(s) used in this video: https://share.zight.com/jkujX8pA