How do I replace column of numbers in excel with their "th" and "nd" ending?

user_8739053 0
Viewed 264

I have a column of numbers in excel that I would like to replace with their two letter ending "th" "nd" as in 13th, or 62nd. How do I do this in excel? I do have a big key that has the number and its ending on a separate sheet.

vlookup excel
1 Answers

Cardinal numbers are normal numeric numbers and look like: 1, 2, 3, 4, etc.

Ordinal numbers have a suffix and look like: 1st, 2nd, 3rd, 4th, etc.

Please note that in ordinal form, numbers are actually text and should only be used for display; Excel will no longer consider them numbers.

If a cardinal number is in A1 this formula will result in its ordinal representation...

=IF(OR(--RIGHT(A1,2)={11,12,13}),"th",IFERROR(CHOOSE(RIGHT(A1),"st","nd","rd"),"th"))

The above formula does NOT need to be array entered. Just a normal confirmation will suffice.

For completeness here is a way to use the above surprisingly concise formula as a UDF. Place the following function in a VBA standard code module...

Function Ordinal(n)
  Ordinal = n & Evaluate("IF(OR(--RIGHT(" & n & ",2)={11,12,13}),""th"",IFERROR(CHOOSE(RIGHT(" & n & "),""st"",""nd"",""rd""),""th""))")
End Function

Now you can call it from a formula on a worksheet. For example, in cell A1 you could have the following formula:

=Ordinal(123)

Or from a VBA routine...

Msgbox Ordinal(21)

And to get and hold an array of the first 255 ordinal numbers in VBA you can use the following variation as a function...

Function Ordinals()
  Ordinals = [ROW(1:255)&IF((--RIGHT(ROW(1:255),2)>10)*(--RIGHT(ROW(1:255),2)<14),"th",IFERROR(CHOOSE(RIGHT(ROW(1:255)),"st","nd","rd"),"th"))]
End Function

Now get the array like so...

Sub Test()
  Dim v  
  v = Ordinals  '<-- v is now a 2d array and holds the first 255 ordinals
  MsgBox v(211, 1)
End Sub
user_35669980
Related