Souptik Roy
In this article, I will show you how to write 1st, 2nd, and 3rd in Excel. While working on an Excel worksheet, you need to do a massive type of work. Sometimes you may need to rank any dataset ordinally. In this article, I will show you how to write 1st 2nd 3rd in Excel properly. I hope this article will increase your excel skill. However, follow the procedures step-by-step. I have added pictures for your better understanding.
Download Practice Workbook
Please download the workbook to practice yourself.
3 Effective Ways to Write 1st 2nd 3rd in Excel
Here I will use the following dataset. You can see that the dataset has three columns B, C, and D called Student Id, Name, and Position. You can see the position has ordinal numbers already. but they are not correctly formatted. I will show you how to write 1st, 2nd, 3rd in Excel with the correct format. Let’s follow the steps of every procedure.
1. Formatting Text Values as Superscripts to Write 1st 2nd 3rd in Excel
In this method, I will show you how to format text values as superscripts to write 1st 2nd 3rd in Excel. This is an easy way. Follow the whole procedure step-by-step. However, I have used this dataset where the positions are not formatted correctly. So, let’s follow the following steps to format the positions.
Steps:
- First, select the last portion of the ordinal positions.
- Then, press Ctrl+1 simultaneously.
- Consequently, a window will pop up like the picture given below.
- In that window, select the Superscript option.
- Then press Ok.
- As a result, the cardinal position will be formatted correctly.
- Then, Fill-Handle from the D5 cell to the D10Â cell.
- Consequently, Excel will show the corrected format of the positions.
This is how you can format text values and know how to write 1st 2nd 3rd in Excel.
Read More: How to Superscript Text in Excel Graph (3 Quick Ways)
2. Adding Superscript for Quick Access Toolbar
In this part of this article, I will show you how to write 1st 2nd 3rd in Excel by adding superscripts to the quick access toolbar. I am sure this will help you to enhance your excel knowledge. Follow the steps given below.
Steps:
- First, select the arrow icon shown in the picture given below.
- Then, select the More Commands option from the pop-up window.
- Select the Quick Access Toolbar in the pop-up window.
- Then, select the Superscript option.
- After that, click on the Add button.
- Then, you will find the superscript option on the other side. Press Ok.
- As a result, you will find the Superscript option in the toolbar.
- Now, select the last texts of the entry of the D5Â cell.
- Then click on the Superscript option.
- As a result, Excel will make the superscript of the ordinal position. Then Fill Handle to copy down this command from D5 to D10Â cell.
- Consequently, Excel will show the following results.
This is the process of writing 1st 2nd 3rd in Excel by using the quick excel toolbar.
Read More: [Fixed!] Excel Superscript Not Working (4 Possible Solutions)
3. Applying Superscript by Inserting Equation
In this method, I will apply superscript by inserting equations in Excel. This is also an easy method. This is the last of this article but not the least. You can see that I have deleted all the positions and the position column is blank. Follow the following steps of this procedure. Hopefully, you will enjoy the whole method.
Steps:
- First, select the Insert tab from your toolbar.
- Then select the Symbols & Equations options respectively.
- In the equation tab, you will find the Structures Click on it.
- Then, select the script option and click on the first option.
- You will find the equation writing expression.
- Then, write down the ordinal numbers.
- As a result, you will find the ordinal rank in the D5.
- Similarly, write down all the positions by following this manually.
This is how you can write ordinal positions in Excel by inserting equations easily.
Read More: How to Add Subscript in Excel Graph (7 Easy Methods)
How to Write 1st 2nd 3rd in Excel Using VBA
You can write the ordinal numbers in Excel using VBA code. Here I will make a user-defined function in Excel using a VBA code to write 1st 2nd 3rd. Hopefully, this process will help you to increase your Excel skill. Follow the following steps.
Steps:
- First, click the Alt+F11 buttons simultaneously.
- Then, press the indicated button shown in the next picture and select the Module
- Then, copy the following code to the module.
Function OrdRank(s_Number As String) As String
Select Case CLng(VBA.Right(s_Number, 1))
Case 1
OrdRank = s_Number & "st"
Case 2
OrdRank = s_Number & "nd"
Case 3
OrdRank = s_Number & "rd"
Case Else
OrdRank = s_Number & "th"
End Select
Select Case VBA.CLng(VBA.Right(s_Number, 2))
Case 11, 12, 13
OrdRank = s_Number & "th"
End Select
End Function
Here, we declared some variables in the VBA code to build a user-defined function called OrdRank and used the Case Statement to apply some conditions.
- Then, select the E5Â cell.
- Then, write down the following formula in the selected cell.
=OrdRank(D5)
- After pressing Enter, excel will show the following result. Then, copy down the formula from E5 to E10Â cells.
- As a result, Excel will show all the ordinal ranks of the D
This is how you will write ordinal positions in excel by inserting a VBA code.
Things to Remember
- You need to bear in mind that the positions added in Method 3 are manual. You cannot fill the following cells by copying them down like fill-handle.
Conclusion
In this article, I have tried to explain how to write 1st, 2nd and 3rd in Excel. I hope you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.
