format cell to show 1st, 2nd, 3rd, 4th et.al.

[JasonVS]

How do you get excel to automatically put in the letters if the cell only will contain a number?
Have several cells with position# (1st, 2nd, 3rd) and would like to just input 1, 2, 3 and have it look like 1st, 2nd, 3rd.
Simple but can't figure it out, nor is it Googleable anywhere...

 

[Gates Is Antichrist]

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

Get to know and love cpearson.com.

 

[Alisa]

Can you enter it in autocorrect?
tools/options/spelling/autocorrect
Then, whenver you type 1, it will automatically correct it to 1st
The only problem with the solution is it will ALWAYS do it, so if you just wanted this to apply to one column or one workbook, it won't help you.

 

[JasonVS]

a good answer and a good suggestion.
Saying there is no ordinal function in Excel sets my mind at ease--I haven't missed it anywhere.

The autofill is a great idea but I need it to be only for a cell.


Want to do it all in one cell, but alas it looks impossible.

Thanks much!

 

[Gates Is Antichrist]

Did you click the link? That's all in one cell, isn't it?

 

[chiello]

Want to do it all in one cell, but alas it looks impossible.

Suppose you want to format cell A1 of Sheet1

Try this:
1)copy the code of "Function OrdinalNumber" in a standard module
2) Copy the following code in the Sheet1 code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("A1").Address And _
Application.WorksheetFunction.IsNumber(Target) Then
Target = OrdinalNumber(Target)
End If
Application.EnableEvents = True
End Sub

Does it work??

Post for feedback.. ciao

 

[chiello]

Sorry, but above code fails if the change involves a range greater than 1 cell. Copy this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count = 1 And Target.Address = Range("A1").Address Then
If Application.WorksheetFunction.IsNumber(Target) Then
Target = OrdinalNumber(Target)
End If
End If
Application.EnableEvents = True
End Sub