1st and 2nd.... positions

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.

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.

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.

Making "significant" progress:

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

Yes, you did make significant progress... Nice going!

One of the problems with my coming into the Excel newsgroups armed basically
with only my knowledge of the compiled VB world is I tend to think in VB and
then translate that to either VBA or spreadsheet formulas. This means that,
when translating, I have to be aware of the differences between how
identically named functions perform. In this case, I completely forgot that
the spreadsheet version of the MOD function, unlike the VB/VBA Mod function,
never returns negative values. I needed to see your formula in order for me
to remember that fact. Had I remembered that back when I first translated my
VB Ordinal function into the spreadsheet formula I originally posted, I
would have abandoned the ABS function call (which was there solely to work
around the fact that VB's Mod function can return negative values) in favor
of the MOD(A1-11,100)2 logical expression you used (although I am sure it
would have taken me some "fooling around time" to arrive at it). Then,
progressing through the "formula shortening" process I posted earlier in
this thread, I would have ended up posting this final formula...

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

As it turns out, it is the same length as your formula above! Now, I used my
originally posted formula as its basis, which accounts for the difference in
the construction of our two formulas; although interestingly, we used all
the same functions (in different ways, of course). However, I am glad that I
didn't come up with this formula yesterday because, more than likely, you
would not have ended up posting the formula that you eventually did. Aside
from the fun and satisfaction of your doing it yourself, I (as someone who
was a math major back in college) find our two different mathematical
constructions, which end up yielding the same result, a fascinating thing to
study.

Rick