Thread: Adding st, nd, rd, th to ranked values Thanks: 0 Likes: 0

1. Adding st, nd, rd, th to ranked values

Hi everyone!

I am using the following formula to rank 20 rows of entries at S5:S24.

Code:
=IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")
What modification do I need to get the texts st, nd, rd, th as ordinal suffix?

Regards  Reply With Quote

2. Re: Adding st, nd, rd, th to ranked values

It's not pretty, but it will work.

Code:
=IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")&IF(AND(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),100)>10,MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),100)<14),"th",CHOOSE(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
Should work to convert any cardinal number into an ordinal number. Note that these values will be stored as text and not as numbers anymore.
Alternatively, you could add the following into column T
Code:
=IF(AND(MOD(ABS(S5),100)>10,MOD(ABS(S5),100)<14),"th",CHOOSE(MOD(ABS(S5),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
This would leave the cardinal number in column S and add the ordinal suffix to column T which would aesthetically LOOK similar, but would allow the value in column S to be used as an actual number.  Reply With Quote

3. Re: Adding st, nd, rd, th to ranked values

Excellent solution @BiocideJ! Thanks a lot!

Using the 1st solution as value turning into text does not affect my purposes.

Just another little one, is there a way that I could get the ordinal suffixes as superscript automatically?  Reply With Quote

4. Re: Adding st, nd, rd, th to ranked values Originally Posted by BiocideJ It's not pretty, but it will work.

Code:
=IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")&IF(AND(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),100)>10,MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),100)<14),"th",CHOOSE(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5),"")),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
A little prettier...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2) Originally Posted by masud8956 Just another little one, is there a way that I could get the ordinal suffixes as superscript automatically?
That would require VBA event code. Can you make use of a VBA solution?  Reply With Quote

5. Re: Adding st, nd, rd, th to ranked values

The following two sub-routines, placed in the relevant worksheets code module, together with the third sub-routine and the accompanying function in a standard module, will automatically apply ordinal formatting to values entered into a range named "Ordinal":
Code:
Private Sub Worksheet_Calculate()
Ordinals ActiveSheet.Range("Ordinal")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Ordinals Target
End Sub
Note: you dont need the code in red unless you also use the OrdVal UDF described later.
Code:
Public NmRng As String
Sub Ordinals(ByVal Target As Range)
Dim oCell As Range
If NmRng <> "" Then _
RefersTo:=ActiveWorkbook.Names.Item("Ordinal") & "," & NmRng
If Intersect(Target, ActiveSheet.Range("Ordinal")) Is Nothing Then Exit Sub
On Error Resume Next
For Each oCell In Target
If IsNumeric(oCell.Value) Then oCell.NumberFormat = OrdFmt(oCell.Value)
Next
End Sub

Function OrdFmt(ByVal Num As Long) As String
Dim Cell As Range
If IsNumeric(Cell.Value) Then
OrdFmt = "#""" & Mid\$("thstndrdthththththth", 1 - 2 * _
((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"
End If
End Function
One limitation of User-Defined Functions (UDFs) is that they can only change the value displayed in the cell that contains the formula. They cannot change any other aspect of the workbook. This means you cant use the UDF to change the cells number format or add it to the named range. They can, however, set a variable's properties, and here's where the workaround takes effect. By setting the NmRng variable and incorporating the code in red, above, you can have a UDF that indirectly adds the target cell's address to the named range and exploit the 'Worksheet_Change' and 'Worksheet_Calculate' events to apply ordinal number formatting to a cell with a formula like:
=OrdVal(A1):
Code:
Function OrdVal(ByVal Num As Long) As Long
Application.Volatile
NmRng = ActiveSheet.Name & "!" & Selection.Address
If InStr(ActiveWorkbook.Names.Item("Ordinal"), NmRng) <> 0 Then NmRng = ""
OrdVal = Num
End Function
Alternatively, the UDF below can use a formula like;
=Ordinal (A1); or
=Ordinal(37),
for any cell on any worksheet to which the event-driven code is attached.
Code:
Function Ordinal(ByVal Num As Long) As Long
Ordinal = Num
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error Resume Next
For Each Cell In ActiveSheet.UsedRange
If UCase(Cell.Formula) Like "=ORDINAL(?*)" Then
Cell.NumberFormat = "#,#""" & Mid\$("thstndrdthththththth", 1 - 2 * _
((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"
End If
Next Cell
End Sub
The underlying values remain available for use as numbers in other formulae.
Of course, if only a limited range of cells in a large workbook can have this kind of formula, it would be wise to narrow the event's target range to just that. The above approach also allows a mix of ordinal and ordinary values to coexist in the target range.
Note: Decimal value ordinals are based on the nearest integer. As well, when driven by the Ordinal Function, the values are converted to strings.  Reply With Quote

6. Re: Adding st, nd, rd, th to ranked values Originally Posted by masud8956 .. turning into text does not affect my purposes.

.. is there a way that I could get the ordinal suffixes as superscript automatically?
Combining Rick's formula from post 4 with some vba, you could try this Worksheet_Change code on a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rData As Range

Set rData = Range("R5:R24")
If Not Intersect(Target, rData) Is Nothing Then
rData.Offset(, 1).Formula = Replace(Replace("=IF(ISNUMBER(^),1+COUNTIF(#,"">""&^),"""")", "#", rData.Address), "^", rData.Cells(1).Address(0, 0))
For Each c In rData.Offset(, 1)
If Len(c.Value) > 0 Then
c.Value = c.Value & Evaluate("MID(""thstndrdth"",MIN(9,2*RIGHT(" & c.Value & ")*(MOD(" & c.Value & "-11,100)>2)+1),2)")
c.Characters(Len(c.Value) - 1, 2).Font.Superscript = True
End If
Next c
End If
End Sub  Reply With Quote

7. Re: Adding st, nd, rd, th to ranked values Originally Posted by masud8956 is there a way that I could get the ordinal suffixes as superscript automatically?
You could also make use of the unicode superscript characters - using Rick's formula, if you copy this directly from the forum the ordinals should be superscript.

=A1&MID("ᵗʰˢᵗⁿᵈʳᵈᵗʰ",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)  Reply With Quote

8. Re: Adding st, nd, rd, th to ranked values Originally Posted by FormR You could also make use of the unicode superscript characters .. Good thinking. So using this idea, which includes Rick's of course, and the OP's original formula, it could all be done at once.

Ordinals

 R S 5 5 16ᵗʰ 6 6 13ᵗʰ 7 25 8ᵗʰ 8 14 11ᵗʰ 9 Text 10 6 13ᵗʰ 11 #N/A 12 45 3ʳᵈ 13 32 4ᵗʰ 14 8 12ᵗʰ 15 23.25 10ᵗʰ 16 90 1ˢᵗ 17 30 6ᵗʰ 18 19 20 27 7ᵗʰ 21 32 4ᵗʰ 22 24 9ᵗʰ 23 6 13ᵗʰ 24 70 2ⁿᵈ

 Cell Formula S5 =IF(ISNUMBER(R5),1+COUNTIF(\$R\$5:\$R\$24,">"&R5)&MID("ᵗʰˢᵗⁿᵈʳᵈᵗʰ",MIN(9,2*RIGHT(1+COUNTIF(\$R\$5:\$R\$24,">"&R5))*(MOD(1+COUNTIF(\$R\$5:\$R\$24,">"&R5)-11,100)>2)+1),2),"")

Excel tables to the web >> Excel Jeanie HTML 4  Reply With Quote

9. Re: Adding st, nd, rd, th to ranked values

Wonderful solutions by all of you!

Each of them worked for me.

Cannot thank you experts enough!!  Reply With Quote

10. Re: Adding st, nd, rd, th to ranked values Originally Posted by masud8956 Wonderful solutions by all of you!

Each of them worked for me.
You have some choice then.  Originally Posted by masud8956 Cannot thank you experts enough!!
You just did.   Reply With Quote

User Tag List

Tags for this Thread

adding, modification, ordinal, suffix, texts  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•