Making "significant" progress:
=A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(ABS (MOD(A1,100)-12)1),2)
=A1&MID("thstndrdthththththth",1+2*RIGHT(A1)*(ABS( MOD(A1,100)-12)1),2)
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(ABS(MOD(A1 ,100)-12)1)+1),2)
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)
--
Biff
Microsoft Excel MVP
"Rick Rothstein (MVP -
VB)" wrote in
message ...
And, because the main function is a one-liner, it lends itself to
translation into a spreadsheet formula (should that be something the OP
would want)...
=A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(ABS (MOD(A1,100)-12)1),2)
That's the most compact version I've seen.
By substituting your RIGHT(A1) function call in place of my MOD(A1,10)
function call, we can save one additional character...
=A1&MID("thstndrdthththththth",1+2*RIGHT(A1)*(ABS( MOD(A1,100)-12)1),2)
although this now exposes it to same error condition that your formula
does when A1 is blank (which may considered a good thing in the end).
Rick
"T. Valko" wrote in message
...
Nice one, Rick. That's the most compact version I've seen. I'll add that
to my library!
--
Biff
Microsoft Excel MVP
"Rick Rothstein (MVP - VB)" wrote in
message ...
And, because the main function is a one-liner, it lends itself to
translation into a spreadsheet formula (should that be something the OP
would want)...
=A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(ABS (MOD(A1,100)-12)1),2)
Rick
"Don Guillett" wrote in message
...
From a post replying to this subject in the programming group
A #NAME? problem with a transferred function
By the way, you might find this one-liner Ordinal function I developed
back
in the compiled VB world (modified for use in Excel) interesting...
Function Ordinal(Cell As Range) As String
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", _
1 - 2 * ((Cell.Value) Mod 10) * _
(Abs((Cell.Value) Mod 100 - 12) 1), 2)
End Function
Rick
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"gorro" wrote in message
...
assuming i want to arrange student positions, how do i get excel to
automatically add say st, nd, rd, th, to 1st, 2nd, 3rd, and 4th
positions?
thaks for answering.