Screenshots: Add ordinals in Excel
Image 1 of 12
Pro tip: Add an ordinal indicator to a value in Excel
Pro tip: Add an ordinal indicator to a value in Excel
Adding an ordinalrnindicator – st, nd, rd, and th – uses a suffix to denote the value’srnposition within a series. For example, 1 becomes 1st, 2 becomes 2nd, 3 becomesrn3rd, and so on. In Excel, you can use a complex formula to create a new string orrnyou can apply several conditional formatting rules to display the indicatorrnwith the value.
rnrn
Excel 2003 users must use the formula solution. If you wantrnto apply the conditional formatting technique, you must have Excel 2007 orrnlater.
rnrn
The rules
rnrn
Knowing the rules and their precedence is imperative. Tryingrnto apply ordinals without knowing the following rules will just make you sad:
rnrn
-
- rn
- Values ending in 0 alwaysrn get th.
rn
-
- Values ending in 1 get st unless the value is 11 or a valuern that ends with 11.
rn
-
- Values ending in 2 get nd unless the value is 12 or a valuern that ends with 12.
rn
-
- Values ending in 3 get rd unless the value is 13 or a valuern that ends with 13.
rn
-
- Everything else gets th.
rn
rnrn
Getting the rules applied in the correct order is the key.rnThe values 11, 12, and 13 certainly throw a monkey wrench into the works, butrnExcel can handle it.
Add ordinals in Excel 1
Add ordinals in Excel 1
In the figure below, I’ve used a formula to combine a valuernand its appropriate ordinal indicator:
rnrn
=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"))
rnrn
This formula has been in use for a long time. If you try arnshorter version, be sure to check the results for values ending with 11, 12,rnand 13 carefully. Most importantly, this formula returns a string, not a value;rnyou can’t refer to the results of the formula in mathematical equations.
rnrn
Although long, the formula is simple. The first part of thernformula accommodates values ending with 11, 12, and 13. The second part of thernformula uses CHOOSE() to handle the rest. I suppose you could simplify bothrncomponents, but I’ve never tried. This works, and I can’t justify the time itrnwould take to rethink it. It works with positive and negative integers,rnignoring decimal components.
rnrnCredit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 2
Add ordinals in Excel 2
A conditional format
rnrn
You can also use a conditional format. This method displaysrnthe indicator with the actual value rather than creating a new string. Thernoriginal value remains a numeric value. You only change the way Excel displaysrnthat value.
rnrn
You’ll need six formulas instead of one; use the formulasrnlisted in Table A.
rnrn
Table A
rnrn
rnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrnrn
| rn
rn |
rn
rn |
rn
rn |
| rn
rn |
rn
rn |
rn
rn |
| rn
rn |
rn
rn |
rn
rn |
| rn
rn |
rn
rn |
rn
rn |
| rn
rn |
rn
rn |
rn
rn |
| rn
rn |
rn
rn |
rn
rn |
rnrn
You must enter thernabove rules in their listed order. There are other routes and other formulas,rnbut this route specifies each rule in ordinal precedence. If you use otherrnrules, be sure to account for the application order, which can get messy – itrnisn’t impossible, but it is morerndifficult to follow.
rnrn
Now, let’s apply the first rule to the values shown below:
rnrn
-
- rn
- Select the data. In thisrn case, that’s A1:H20.
rn
-
- In the Styles group on thern Home tab, click Conditional Formatting.
rn
-
- Choose New Rule.
rn
-
- In the New Formatting Rulern dialog, select the last option in the upper pane: Use A Formula Torn Determine Which Cells To Format.
rn
-
- Enter the “th”rn rule for the 4-9 rule:
rn
rnrn
=AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)
rnrnCredit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 3
Add ordinals in Excel 3
-
- rn
- Click Format.
rn
-
- Click the Number tab.
rn
-
- In the Category list,rn select Custom.
rn
-
- In the Type field, enterrn the custom code: 0″th”
rn
rnrn
You can skip that last step if you like. I’m also usingrncolor to highlight the formatted values. Doing so creates a nice visual trailrnto follow, but you probably won’t want to apply color to the values you formatrnin your own sheets. Click the Fill tab, choose a color, and click OK.
rnrn
Click OK twice. This first rule adds th to values endingrnwith the digits 4 through 9.
rnrn
Repeat this process for the remaining rules, being carefulrnto add them in the listed order. When you enter the rules for 1, 2, and 3,rnyou’ll notice that Excel also formats the values ending with 11, 12, and 13,rnwhich is incorrect. Don’t worry because the final rule for 11, 12, and 13 willrnoverride the earlier rules where necessary.
rnrnCredit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 4
Add ordinals in Excel 4
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 5
Add ordinals in Excel 5
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 6
Add ordinals in Excel 6
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 7
Add ordinals in Excel 7
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 8
Add ordinals in Excel 8
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 9
Add ordinals in Excel 9
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 10
Add ordinals in Excel 10
Credit: Image by Susan Harkins for TechRepublic
Add ordinals in Excel 11
Add ordinals in Excel 11
Bottom line
rnrnUsing conditional formatsrnto apply ordinal indicators can be problematic. First, you must be careful tornconsider precedence when entering the rules. My way isn’t the only way and itrnisn’t the most efficient, but it is easy to follow. Second, if you’re workingrnwith other conditional formats, you must continue to consider precedence. Yourncan combine rules and use the Stop If True property appropriately whenrncombining this set of rules with others. Next month, I’ll show you how to addrnordinal indicators to dates.
rnrnCredit: Image by Susan Harkins for TechRepublic
