Hi,

You can't actually do that with straight formatting. If you enter 421st in
a cell and to format it you will need to select the st portion of the entery
and choose Format, Cells, Font tab, Superscript. Now fill it down and you
will see what Excel can do.

You could write a macro to do this, but you can't use a formula approach
because Excel does not allow you to format part of a formula.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jokah.D" wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?

Actually it can be done my friend. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you anyway for your reply


"Shane Devenshire" wrote:

Hi,

You can't actually do that with straight formatting. If you enter 421st in
a cell and to format it you will need to select the st portion of the entery
and choose Format, Cells, Font tab, Superscript. Now fill it down and you
will see what Excel can do.

You could write a macro to do this, but you can't use a formula approach
because Excel does not allow you to format part of a formula.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jokah.D" wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?