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.

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.

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.

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.

Thanks! One possible addition you may want to consider...

=0+A1&MID("thstndrdthththththth",1+2*MOD(A1,10)*(A BS(MOD(A1,100)-12)1),2)

Adding the 0 makes the formula return 0th rather than th when A1 is blank.
Unlike the formula you posted, there is no feedback error when A1 is blank,
so treating blanks like zeroes should probably be an acceptable compromise;
although I guess a standard IF(A1="","",....) wrapper is still a
possibility.

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.

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.

We can save another 3 characters by doing this...

=A1&MID("thstndrdth",1+2*MIN(4,MOD(A1,10))*(ABS(MO D(A1,100)-12)1),2)

but doing so adds another function call to the mix, so I'm guessing that
would make it a less desireable alternative.

Rick

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.

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.

You guys are dangerously good! it worked out perfectly. Only that i cant
understand how you got to that. I'm grateful.

Another small problem comes up again! That is: is it possible to get in
another column, 1st, for the highest mark, 2nd, for the next, ........

Thaks. With you, no fears.
gorro

"Rick Rothstein (MVP - VB)" wrote:

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.

To combine a rank (standard Excel RANK) and the ordinal together:

Assume the numbers to rank are in the range A2:A11. Also assuming that there
is nothing in the range but numbers. No empty cells, no text, no errors, no
booleans, no formula blanks...

Enter this formula in B2 and copy down:

=RANK(A2,A$2:A$11)&MID("thstndrdth",MIN(9,2*RIGHT( RANK(A2,A$2:A$11))*(MOD(RANK(A2,A$2:A$11)-11,100)2)+1),2)

--
Biff
Microsoft Excel MVP


"gorro" wrote in message
...
You guys are dangerously good! it worked out perfectly. Only that i cant
understand how you got to that. I'm grateful.

Another small problem comes up again! That is: is it possible to get in
another column, 1st, for the highest mark, 2nd, for the next, ........

Thaks. With you, no fears.
gorro

"Rick Rothstein (MVP - VB)" wrote:

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.

Or, you can use a separate column for the rank and another column for the
odinal:

B2: =RANK(A2,A$2:A$11)

Copied down

C2:

=B2&MID("thstndrdth",MIN(9,2*RIGHT(B2)*(MOD(B2-11,100)2)+1),2)

Copied down

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
To combine a rank (standard Excel RANK) and the ordinal together:

Assume the numbers to rank are in the range A2:A11. Also assuming that
there is nothing in the range but numbers. No empty cells, no text, no
errors, no booleans, no formula blanks...

Enter this formula in B2 and copy down:

=RANK(A2,A$2:A$11)&MID("thstndrdth",MIN(9,2*RIGHT( RANK(A2,A$2:A$11))*(MOD(RANK(A2,A$2:A$11)-11,100)2)+1),2)

--
Biff
Microsoft Excel MVP


"gorro" wrote in message
...
You guys are dangerously good! it worked out perfectly. Only that i cant
understand how you got to that. I'm grateful.

Another small problem comes up again! That is: is it possible to get in
another column, 1st, for the highest mark, 2nd, for the next, ........

Thaks. With you, no fears.
gorro

"Rick Rothstein (MVP - VB)" wrote:

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.

Hello,

Or
=RANK(A2,A$2:A$11)&CHOOSE(MIN(4,RIGHT(RANK(A2,A$2: A$11),
1)),"st","nd","rd","th")
and copy down.

FastExcel says its 13.6 microsecs per formula (compared to Biff's one
which takes about 16.4).

Regards,
Bernd

Hello,

Why not
Function Ordinal2(Cell As Range) As String
Ordinal2 = Cell.Value & Mid("thstndrdthththththth", 1 + 2 *
Right(Cell.Value, 1), 2)
End Function
?

Regards,
Bernd

Why not
Function Ordinal2(Cell As Range) As String
Ordinal2 = Cell.Value & Mid("thstndrdthththththth", 1 + 2 *
Right(Cell.Value, 1), 2)
End Function
?

Try 111 as but one example.

Rick

Hi Rick,

Right.

Regards,
Bernd

Try this:

A1 = some number

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"}))

--
Biff
Microsoft Excel MVP


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