Is there anyway to automatically convert 1 into 1st, 2 into 2nd, and so on

[slam]

I'd like to type 1 in the cell, hit enter, and it be automatically converted to 1st. I need to do this for 16 rows so 1 (1st) through 16 (16th), and I will not always be entering the numbers in the same cells.

Thanks

 

[T. Valko]

Oh of course, I should have remembered to change it to COUNTA!

Thanks

Good deal. Thanks for the feedback!

 

[T. Valko]

Here's a formula you can use in another column.

Let's assume the numbers are in the range A1:A16.

Enter this formula in B1 and copy down to B16:

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

I will go with Mr. valko. The VBA code is only applicable in between 1 to 16, after 16 it cant work. But the formula is excellent !!!! thanks Mr. Valko for this formula.

If anyone is interested in that formula we can thank Microsoft MVP Rick Rothstein who did the "heavy lifting" and came up with the basic methodology. I just added a tweak or 2.

http://tinyurl.com/OrdinalNums

 

[Rick Rothstein]

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

Both of the above codes work with any size valid Excel number.

 

[Biz]

If anyone is interested in that formula we can thank Microsoft MVP Rick Rothstein who did the "heavy lifting" and came up with the basic methodology. I just added a tweak or 2.

http://tinyurl.com/OrdinalNums

Hi Biff,

Awesome formula but I thought if some entered zero then blank would be good.

=IF(A1=0,"",A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2))

Biz

 

[Rick Rothstein]

If anyone is interested in that formula we can thank Microsoft MVP Rick Rothstein who did the "heavy lifting" and came up with the basic methodology. I just added a tweak or 2.

http://tinyurl.com/OrdinalNums

Biff is being too kind regarding his role in developing that formula... if you check the link he posted, I think you will see he added more than just a "tweak or 2".

 

[T. Valko]

Hi Biff,

Awesome formula but I thought if some entered zero then blank would be good.

=IF(A1=0,"",A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2))

Biz

Yeah, you could do that but in this application the OP is only entering numbers from 1 to 16.

To make sure the user enters a valid number it may be better to set up a data validation rule rather than putting the error checking in the formula.

Numbers only
Integers only
Numbers from 1 to 16
No duplicates

In another thread by this OP, that is what they have opted to do.