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

 

[tigeravatar]

slam,

Worksheet_Change event 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

 

[T. Valko]

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

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)

 

[slam]

slam,

Worksheet_Change event 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

Excellent, thank you!

 

[GoKats78]

Just do away with the "st", "nd", and "rd"...
oneth, twoth, threeth...you could really mess we someone if the also use this sheet...

 

[RAMU]

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.

 

[slam]

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.....

 

[T. Valko]

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.

You're welcome. Thanks for the feedback!

 

[T. Valko]

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.....

So, do you have the ordinal numbers in Australia!$D$2:$D$17 ?

If so, searching for 1st (or 2nd, 3rd, 4th, etc.) should work.

However, now the COUNT function won't work since the ordinals are TEXT strings and are not numbers.

Replace COUNT with COUNTA.

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

 

[slam]

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

Thanks