How to convert cardinal number to ordinal number in Excel?
If you have a data range as follow screenshot shown, and you want to convert the cardinal number in the Rank Column to ordinal number, how can you do? Maybe you can change them one by one manually, but here I can introduce some tricks to solve the conversion quickly in Excel.
Convert cardinal number to ordinal number with Defined Function
Convert cardinal number to ordinal number with Convert Number to Ordinal 
Comparison of Methods for Converting Cardinal Numbers to Ordinal Numbers in Excel
Ordinal numbers such as 1st, 2nd, 3rd, and 4th are widely used in rankings, competition results, reports, and ordered lists. The following table compares the methods introduced in this article.
| Feature | User Defined Function (VBA) | Kutools Convert Number to Ordinal |
|---|---|---|
| Convert cardinal numbers to ordinal numbers | ✔ | ✔ |
| Batch conversion | Requires filling the custom formula | ✔ |
| Requires VBA | ✔ | ✘ |
| Requires formulas | ✔ (Custom Function) | ✘ |
| One-click conversion | ✘ | ✔ |
| Works directly on selected cells | ✘ | ✔ |
| Supports Undo (Ctrl + Z) | ✘ | ✔ |
| Requires enabling macros | ✔ | ✘ |
| Programming knowledge required | Basic VBA knowledge | None |
| Setup process | Insert the VBA module, create the custom function, enter the formula, and fill it down. | Select the numbers, then click Kutools > Content > Convert Number to Ordinal. |
| Best for | Users familiar with VBA and custom worksheet functions | Quickly converting rankings and ordered lists without formulas or VBA |
| Ease of use | Moderate | Very easy |
Convert cardinal number to ordinal number with Defined Function
To convert cardinal number to ordinal number, there is no direct way to solve in Excel, but now I tell you a Defined Function to help you.
1. Press Alt and F11 keys at the same time to open the Microsoft Visual Basic for Application window.
2. Then click Insert > Module and copy the follow VBA into the Module window.
VBA: Convert cardinal number to ordinal number.
Function Addth(pNumber As String) As String
'Updateby20141027
Select Case CLng(VBA.Right(pNumber, 1))
Case 1
Addth = pNumber & "st"
Case 2
Addth = pNumber & "nd"
Case 3
Addth = pNumber & "rd"
Case Else
Addth = pNumber & "th"
End Select
Select Case VBA.CLng(VBA.Right(pNumber, 2))
Case 11, 12, 13
Addth = pNumber & "th"
End Select
End Function3. Save the code and close the window, and type this formula =Addth(C2) into a blank cell (cell C2 is the cell that you want to convert to ordinal number), then press Enter key. And if you need, you can drag the autofill handle to fill the range cells to apply the formula.
Now you can see the cardinal numbers are converted to ordinal numbers.
Convert cardinal number to ordinal number with Convert Number to Ordinal
For some Excel green hands, the Defined Function may be somewhat complex, but do worry, if you have install Kutools for Excel, here is a Convert Number to Ordinal utility can help you easily convert cardinal numbers to ordinal number by one click!.
After free installing Kutools for Excel, please do as below:
1. Select the numbers you want to convert to ordinal numbers. See screenshot:
2. Click Kutools > Content > Convert Number to Ordinal. See screenshot:
Then the selected numbers are converted to ordinal numbers.
Tip: This utility supports Undo (Ctrl + Z).
Demo: Cardinal Number To Ordinal Number
Conclusion
Excel does not provide a built-in feature for converting cardinal numbers directly into ordinal numbers. The User Defined Function (VBA) creates a reusable custom worksheet function that automatically appends the correct ordinal suffix. While effective, it requires enabling macros and some familiarity with VBA before it can be used.
Kutools for Excel's Convert Number to Ordinal offers a much simpler solution. With a single click, it converts the selected numbers into ordinal values such as 1st, 2nd, and 3rd, supports batch processing, and even allows users to undo the conversion with Ctrl + Z. This makes it the fastest and most convenient option for formatting rankings, positions, and ordered lists in Excel.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in