Skip to content

TechRepublic

Trending Now

Laptop featuring MuleSoft Logo.
Image: Adobe Stock
Article

MuleSoft Connect 2022: Automation fuels digital transformation

Hacker in data security concept. Hacker using laptop. Hacking the Internet. Cyber attack.
Image: Adobe Stock
Article

The business of hackers-for-hire threat actors

Opensea NFT non-fungible token marketplace
Image: Proxima Studio/Adobe Stock
Article

Data breach of NFT marketplace OpenSea may expose customers to phishing attacks

Internet technology concept
Image: tippapatt/Adobe Stock
Article

Best IT management software for 2022

  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • TechRepublic Premium
  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • See All Topics
  • Sponsored
  • Newsletters
  • Forums
  • Resource Library
TechRepublic Premium

Account Information

Join or sign in

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.

Use Facebook
Use Linkedin

Join or sign in

We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.

Welcome back!

Invalid email/username and password combination supplied.

Reset password

An email has been sent to you with instructions on how to reset your password.

Welcome to TechRepublic!

All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).

Loading
Microsoft

Screenshots: Add ordinals in Excel

By Susan Harkins November 20, 2013, 10:31 AM PST

Image
1
of 12
Previous Next

20_ordinal_excel.png
20_ordinal_excel.png
Screenshots: Add ordinals in Excel

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.

2013251.JPG
2013251.JPG
Screenshots: Add ordinals in Excel

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

2013252.JPG
2013252.JPG
Screenshots: Add ordinals in Excel

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

4-9

rn

rn

th

rn

rn

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

rn

rn

0

rn

rn

th

rn

rn

=MOD(ABS(A1),10)=0

rn

rn

1

rn

rn

st

rn

rn

=MOD(ABS(A1),10)=1

rn

rn

2

rn

rn

nd

rn

rn

=MOD(ABS(A1),10)=2

rn

rn

3

rn

rn

rd

rn

rn

=MOD(ABS(A1),10)=3

rn

rn

11, 12, 13

rn

rn

th

rn

rn

=AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14)

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

    1. rn

    2. Select the data. In thisrn case, that’s A1:H20.

rn

    1. In the Styles group on thern Home tab, click Conditional Formatting.

rn

    1. Choose New Rule.

rn

    1. In the New Formatting Rulern dialog, select the last option in the upper pane: Use A Formula Torn Determine Which Cells To Format.

rn

    1. 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

2013253.JPG
2013253.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 3

Add ordinals in Excel 3

    1. rn

    2. Click Format.

rn

    1. Click the Number tab.

rn

    1. In the Category list,rn select Custom.

rn

    1. 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

2013254.JPG
2013254.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 4

Add ordinals in Excel 4

Credit: Image by Susan Harkins for TechRepublic

2013255.JPG
2013255.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 5

Add ordinals in Excel 5

Credit: Image by Susan Harkins for TechRepublic

2013256.JPG
2013256.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 6

Add ordinals in Excel 6

Credit: Image by Susan Harkins for TechRepublic

2013257.JPG
2013257.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 7

Add ordinals in Excel 7

Credit: Image by Susan Harkins for TechRepublic

2013258.JPG
2013258.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 8

Add ordinals in Excel 8

Credit: Image by Susan Harkins for TechRepublic

2013259.JPG
2013259.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 9

Add ordinals in Excel 9

Credit: Image by Susan Harkins for TechRepublic

2013260.JPG
2013260.JPG
Screenshots: Add ordinals in Excel

Add ordinals in Excel 10

Add ordinals in Excel 10

Credit: Image by Susan Harkins for TechRepublic

2013261.JPG
2013261.JPG
Screenshots: Add ordinals in Excel

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

  • Microsoft
  • Software
  • Account Information

    Share with Your Friends

    Screenshots: Add ordinals in Excel

    Your email has been sent

Share: Screenshots: Add ordinals in Excel
Image of Susan Harkins
By Susan Harkins
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
  • Account Information

    Contact Susan Harkins

    Your message has been sent

  • |
  • See all of Susan's content

TechRepublic Premium

  • TechRepublic Premium

    Industrial Internet of Things: Software comparison tool

    IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and ...

    Downloads
    Published:  May 26, 2022, 5:00 PM PDT Modified:  May 28, 2022, 8:00 AM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    How to recruit and hire an Operations Research Analyst

    Recruiting an Operations Research Analyst with the right combination of technical expertise and experience will require a comprehensive screening process. This Hiring Kit provides an adjustable framework your business can use to find, recruit and ultimately hire the right person for the job.This hiring kit from TechRepublic Premium includes a job description, sample interview questions ...

    Downloads
    Published:  May 19, 2022, 5:00 PM PDT Modified:  May 21, 2022, 12:00 PM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Quick glossary: Industrial Internet of Things

    The digital transformation required by implementing the industrial Internet of Things (IIoT) is a radical change from business as usual. This quick glossary of 30 terms and concepts relating to IIoT will help you get a handle on what IIoT is and what it can do for your business.. From the glossary’s introduction: While the ...

    Downloads
    Published:  May 19, 2022, 5:00 PM PDT Modified:  May 21, 2022, 12:00 PM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Software Procurement Policy

    Procuring software packages for an organization is a complicated process that involves more than just technological knowledge. There are financial and support aspects to consider, proof of concepts to evaluate and vendor negotiations to handle. Navigating through the details of an RFP alone can be challenging, so use TechRepublic Premium’s Software Procurement Policy to establish ...

    Published:  April 14, 2022, 5:00 PM PDT Modified:  April 16, 2022, 1:00 PM PDT Read More See more TechRepublic Premium

Services

  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information

Explore

  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • TechRepublic
  • TechRepublic on Twitter
  • TechRepublic on Facebook
  • TechRepublic on LinkedIn
  • TechRepublic on Flipboard
© 2022 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice