Ordinal Numbers In Excel

 An ordinal number is a number that expresses position in a series, such as 1st, 2nd, or 3rd.  Excel does not have a built in method for appending the suffix to a number.  However, you can very easily write a function, either worksheet formula or a VBA function, to create the suffix.  Worksheet Formula The following formula will return the number in A1 with the suffix appended:  =A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th", CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th")) For example, if A1 contained 23, the formula will return 23rd.   VBA Function The following VBA function will return the suffix (only):  Function OrdinalSuffix(ByVal Num As Long) As String Dim N As Long Const cSfx = "stndrdthththththth" ' 2 char suffixes N = Num Mod 100 If ((Abs(N) >= 10) And (Abs(N) <= 19)) _ Or ((Abs(N) Mod 10) = 0) Then OrdinalSuffix = "th" Else OrdinalSuffix = Mid(cSfx, _ ((Abs(N) Mod 10) * 2) - 1, 2) End If End Function This function does not return the "formatted" number (e.g., "23rd"). It returns only the  appropriate suffix (e.g., "rd").  You can call this directly from a worksheet cell, as follows:  =A1&OrdinalSuffix(A1) To return the ordinal number, use:  Function OrdinalNumber(ByVal Num As Long) As String Dim N As Long Const cSfx = "stndrdthththththth" ' 2 char suffixes N = Num Mod 100 If ((Abs(N) >= 10) And (Abs(N) <= 19)) _     Or ((Abs(N) Mod 10) = 0) Then     OrdinalNumber= Format(Num) & "th" Else    OrdinalNumber= Format(Num) & Mid(cSfx, _      ((Abs(N) Mod 10) * 2) - 1, 2) End If End Function   You can call this directly from a worksheet cell, as follows:  =OrdinalNumber(A1) Or you can call it from other VBA procedures, as follows:  Msgbox "Result Is: " & 5 & OrdinalSuffix(5) Both examples above properly handle the case of the "teenth" numbers, all of which take a "th" suffix, unlike their other counterparts in other "decades" (e.g., 13th and 23rd).  Of course, both examples are written for USA-English language conventions. You may have to change some of the code for other language implementations.