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

slam said:

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

Click to expand...

tigeravatar said:

slam,

Worksheet_Change event code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Formula
        Case 1:       Target.Value = Target.Value & "st"
        Case 2:       Target.Value = Target.Value & "nd"
        Case 3:       Target.Value = Target.Value & "rd"
        Case 4 To 16: Target.Value = Target.Value & "th"
    End Select
    Application.EnableEvents = True
    
End Sub

Hope that helps,
~tigeravatar

Click to expand...

RAMU said:

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.

Click to expand...

slam said:

How do I now change this formula to find 1st instead of 1?

=IF(COUNT(Australia!$D$2:$D$17),INDEX(Australia!$B$2:$B$17,MATCH(1,Australia!$D$2:$D$17,0)),"")

I can't just replace that 1 with 1st or "1st" apparently.....

Click to expand...