Screenshots: Add ordinals in Excel

Add ordinals in Excel 1

In the figure below, I've used a formula to combine a value and its appropriate ordinal indicator:

=value&IF(AND(MOD(ABS(value),100)>10,MOD(ABS(value),100)<14),"th",CHOOSE(MOD(ABS(value),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

This formula has been in use for a long time. If you try a shorter version, be sure to check the results for values ending with 11, 12, and 13 carefully. Most importantly, this formula returns a string, not a value; you can't refer to the results of the formula in mathematical equations.

Although long, the formula is simple. The first part of the formula accommodates values ending with 11, 12, and 13. The second part of the formula uses CHOOSE() to handle the rest. I suppose you could simplify both components, but I've never tried. This works, and I can't justify the time it would take to rethink it. It works with positive and negative integers, ignoring decimal components. 

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 2

A conditional format

You can also use a conditional format. This method displays the indicator with the actual value rather than creating a new string. The original value remains a numeric value. You only change the way Excel displays that value.

You'll need six formulas instead of one; use the formulas listed in Table A.

Table A

4-9	th	=AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)
0	th	=MOD(ABS(A1),10)=0
1	st	=MOD(ABS(A1),10)=1
2	nd	=MOD(ABS(A1),10)=2
3	rd	=MOD(ABS(A1),10)=3
11, 12, 13	th	=AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14)

You must enter the above rules in their listed order. There are other routes and other formulas, but this route specifies each rule in ordinal precedence. If you use other rules, be sure to account for the application order, which can get messy - it isn't impossible, but it is more difficult to follow.

Now, let's apply the first rule to the values shown below:

1. Select the data. In this case, that's A1:H20.
2. In the Styles group on the Home tab, click Conditional Formatting.
3. Choose New Rule.
4. In the New Formatting Rule dialog, select the last option in the upper pane: Use A Formula To Determine Which Cells To Format.
5. Enter the "th" rule for the 4-9 rule:

=AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 3

1. Click Format.
2. Click the Number tab.
3. In the Category list, select Custom.
4. In the Type field, enter the custom code: 0"th"

You can skip that last step if you like. I'm also using color to highlight the formatted values. Doing so creates a nice visual trail to follow, but you probably won't want to apply color to the values you format in your own sheets. Click the Fill tab, choose a color, and click OK.

Click OK twice. This first rule adds th to values ending with the digits 4 through 9.

Repeat this process for the remaining rules, being careful to add them in the listed order. When you enter the rules for 1, 2, and 3, you'll notice that Excel also formats the values ending with 11, 12, and 13, which is incorrect. Don't worry because the final rule for 11, 12, and 13 will override the earlier rules where necessary. 

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 4

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 5

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 6

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 7

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 8

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 9

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 10

Credit: Image by Susan Harkins for TechRepublic

Add ordinals in Excel 11

Bottom line

Using conditional formats to apply ordinal indicators can be problematic. First, you must be careful to consider precedence when entering the rules. My way isn't the only way and it isn't the most efficient, but it is easy to follow. Second, if you're working with other conditional formats, you must continue to consider precedence. You can combine rules and use the Stop If True property appropriately when combining this set of rules with others. Next month, I'll show you how to add ordinal indicators to dates. 

Credit: Image by Susan Harkins for TechRepublic