"Ron Rosenfeld" wrote in message
...
On Fri, 16 Apr 2010 10:08:05 +0100, "Peter T" <peter_t@discussions wrote:

JLGWhiz, afraid that wasn't quite right either! It didn't handle 11-13th
correctly, following also caters for multiple ordinals

test string
"August 1st, bend 2nd, third 3rd, 4th, 10th, 11th 101st 111th 4thousand"

Sub Test1()
Dim rng As Range
Dim cel As Range

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , 2)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cel In rng
SuperNum cel
Next
End If
End Sub

Sub test2()
SuperNum ActiveCell
End Sub

Sub SuperNum(rCell As Range)
Dim n As Long, pos As Long, start As Long
Dim s As String, sNum As String
Dim Target As Range
Dim vData, v, vFlag
Dim arr()

arr = Array("th", "st", "nd", "rd")

Set Target = Selection
If rCell.HasFormula = False Then
vData = rCell.Value
If VarType(vData) = vbString Then
vFlag = rCell.Font.Superscript

If IsNull(vFlag) Then vFlag = True
If vFlag Then rCell.Font.Superscript = False
s = rCell.Value
If Len(s) 2 Then
For Each v In arr
pos = 0
start = 2
pos = -1
While pos
pos = InStr(start, s, v)
If pos Then
sNum = Mid$(s, pos - 1, 1)

n = Val(sNum)
If n = 0 Then
If sNum = "0" Then n = -1
End If

If n Then
If pos + 1 < Len(s) Then
If Mid$(s, pos + 2, 1) _
Like "[ ,]" = False Then n = 0
End If
End If
If n Then
rCell.Characters(pos, 2).Font.Superscript
=
True
End If
start = pos + 1
End If
Wend
Next
End If
End If
End If
End Sub

re Like "[ ,]"
include any other characters that might be allowed after an ordinal, such
as
space or comma.

Looks like a lot of code but I think it should be the fastest approach
here

Regards,
Peter T

This will superscript the ordinal even if it is not the correct one for
the
value.

I had thought of that and could be adapted (the previous version did) but
thought probably not worth bothering with

It also fails to recognize some legitimate constructs

E.g. Test strings

"101th vs 101st"
"May 21st-Jun 16th"

To cater for that particular one, ie the dash, amend the Like string as I
had suggested previously -

Like "[ ,-]" = False Then n = 0

Now let me pop over to yours and make a suggestion :-)

Regards,
Peter Thornton