How to Write 1st 2nd 3rd in Excel (3 Easy Ways)

We will use the following dataset. It has three columns B, C, and D for the Student ID, Name, and Position. You can see the position has ordinal numbers already, but they are not correctly formatted. We will rewrite 1^st, 2^nd, and 3^rdin Excel in the correct format with superscript.

Method 1 – Formatting Text Values as Superscripts to Write 1^st, 2^nd, 3^rd in Excel

We have used the sample dataset where the positions are not formatted correctly.

Steps:

Select the last portion of the ordinal positions.
Press Ctrl + 1.

A window will pop up like the picture given below. Select the Superscript option.
Press OK.

The cardinal position will be formatted correctly.
Drag the Fill Handle icon from the D5 cell to the D10 cell.

Excel will show the corrected format of the positions.

Method 2 – Adding Superscript to the Quick Access Toolbar

Steps:

Select the arrow icon on the main file ribbon.
Select the More Commands option from the drop-down.

Select the Quick Access Toolbar in the pop-up window.
Select the Superscript option.
Click on the Add button.

You will find the superscript option on the other side. Press OK.

You will find the Superscript option in the toolbar.

Select the string in the D5 cell that you need to put into superscript (“st”).
Click on the Superscript option.

Excel will format the ordinal properly.
Use the Fill Handle to copy down this command from the D5 to the D10 cell.

You will get the following results.

Method 3 – Applying Superscript by Inserting an Equation

Let’s remove the text from the cells in the last column in the sample dataset, then fill it with proper values.

Steps:

Select the Insert tab from your toolbar.
Select the Symbols and Equations options.

In the equation tab, you will find Structures. Click on it.
Select the script option and click on the first option.

You will find the equation writing expression.
Write down the ordinal numbers.

You will find the ordinal rank in cell D5.

Write down all the positions manually.

How to Write 1st 2nd 3rd in Excel Using VBA

Steps:

Press Alt+F11 to open the VBA window.
Click on Insert and select Module.

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.