Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    117
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,733
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by BiocideJ; Sep 18th, 2019 at 02:08 PM.
    I use Excel 365.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  3. #3
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    117
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,250
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Adding st, nd, rd, th to ranked values

    Quote Originally Posted by BiocideJ View Post
    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)



    Quote Originally Posted by masud8956 View Post
    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?
    Last edited by Rick Rothstein; Sep 18th, 2019 at 11:39 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default 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 _
      ActiveWorkbook.Names.Add Name:="Ordinal", _
      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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Adding st, nd, rd, th to ranked values

    Quote Originally Posted by masud8956 View Post
    .. 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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,091
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Adding st, nd, rd, th to ranked values

    Quote Originally Posted by masud8956 View Post
    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)
    [code]your code[/code]

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Adding st, nd, rd, th to ranked values

    Quote Originally Posted by FormR View Post
    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

    RS
    5516ᵗʰ
    6613ᵗʰ
    7258ᵗʰ
    81411ᵗʰ
    9Text
    10613ᵗʰ
    11#N/A
    12453ʳᵈ
    13324ᵗʰ
    14812ᵗʰ
    1523.2510ᵗʰ
    16901ˢᵗ
    17306ᵗʰ
    18
    19
    20277ᵗʰ
    21324ᵗʰ
    22249ᵗʰ
    23613ᵗʰ
    24702ⁿᵈ

    Spreadsheet Formulas
    CellFormula
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    117
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Adding st, nd, rd, th to ranked values

    Quote Originally Posted by masud8956 View Post
    Wonderful solutions by all of you!

    Each of them worked for me.
    You have some choice then.



    Quote Originally Posted by masud8956 View Post
    Cannot thank you experts enough!!
    You just did.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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