Adding "st, nd, rd" to a number

[PatrickinDallas]

I saw a post on here a while back on how to add st, nd, rd, th, to a number so that it would read 1st, 2nd, 3rd, 4th, etc.

Can anyone help me find that or repost how to do it?

Thanks.

 

[Oaktree]

Welcome to the board.

See:

http://www.cpearson.com/Excel/ordinal.htm

 

[PatrickinDallas]

Thanks .. works great.

 

[RomulusMilea]

Hi,

Or try this one (input cell is B1):

=B1&IF(OR(VALUE(RIGHT(B1,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(B1))={1,2,3}),CHOOSE(RIGHT(B1),"st","nd","rd"),"th"))

Adapt to your needs...

Romulus.

 

[Armando Montes]

Some others:
http://www.mrexcel.com/forum/s<WBR>howthread.php?t=574738
=A1&MID("thstndrdth",MIN(9,2*R<WBR>IGHT(A1)*(MOD(A1-11,100)>2)+1)<WBR>,2)

http://www.ozgrid.com/forum/sh<WBR>owthread.php?t=146638
=IF(ISNUMBER(G8),IF(RIGHT(G8,1<WBR>)="1","st",IF(RIGHT(G8,1)="2",<WBR>"nd",IF(RIGHT(G8,1)="3","rd","<WBR>th"))),"")


http://www.mrexcel.com/forum/s<WBR>howthread.php?t=574738&page=2
Here is a VB solution you may find interesting... it leaves the value in the cell as a numeric value and changes the Cell Format to affix the ordinal suffix; that way, you can still do calculations with the number as a numerical value, but it will look like you want inside the cell itself...

Private Sub Worksheet_Change(ByVal Target As Range)
  Target.NumberFormat = "0""" & Mid$("thstndrdthththththth", 1 - 2 * ((Target.Value) Mod 10) * (Abs((Target.Value) Mod 100 - 12) > 1), 2) & """"
End Sub

If, on the other hand, you don't need the number to be a numerical value and you will be using the cell's value inside another text string, then this will physically change the cell value from a numerical value to a number string with the orderinal suffix concatenated on to it...

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Target.Value = Target.Value & Mid$("thstndrdthththththth", 1 - 2 * ((Target.Value) Mod 10) * (Abs((Target.Value) Mod 100 - 12) > 1), 2)
  Application.EnableEvents = True
End Sub