Format cells, category, postion

  • Thread starter Thread starter Nick H
  • Start date Start date
N

Nick H

Is there anyway to make a number into a postion (e.g. add
the st in 1st, or nd in 2nd), the difficult is that it
needs to go on a cell with a formula in, so it can change
from 1, 2, 3 etc.

Thank you
 
You can't do this as a formatting, but you could add a formula to
another cell: with a number greater than zero in cell A1 to return the
ordinal:

=IF(A1>0,A1&IF(AND(A1>10,A1<20),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd
","rd","th","th","th","th","th","th")),"")

Watch the line wrapping....

HTH,
Bernie
MS Excel MVP
 
Nick
If you need to apply the "st"... to a formula, just replace the A1 before the & with your formula, in the formulas from the previous responses

If you need to use this number in another formula the substitut

VALUE(LEFT(A1,LEN(A1)-2)

in any formula that references A1, A1 being the cell with the "st"..

Good Luck
Mark Graesse
(e-mail address removed)

----- Nick H wrote: ----

Is there anyway to make a number into a postion (e.g. add
the st in 1st, or nd in 2nd), the difficult is that it
needs to go on a cell with a formula in, so it can change
from 1, 2, 3 etc

Thank yo
 
Thanks guys!!!

-----Original Message-----
You can't do this as a formatting, but you could add a formula to
another cell: with a number greater than zero in cell A1 to return the
ordinal:

=IF(A1>0,A1&IF(AND(A1>10,A1<20),"th",CHOOSE(MOD(A1,10) +1,"th","st","nd
","rd","th","th","th","th","th","th")),"")

Watch the line wrapping....

HTH,
Bernie
MS Excel MVP




.
Click to expand...
 
=IF(A1>0,A1&IF(AND(A1>10,A1<20),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd
","rd","th","th","th","th","th","th")),"")
Click to expand...

Slight correction:

=IF(A1>0,A1&IF(AND(MOD(A1,100)>10,MOD(A1,100)<20),"th",CHOOSE(MOD(A1,10)+
1,"th","st","nd","rd","th","th","th","th","th","th")),"")
 
Why the change?

--

Regards,

Peo Sjoblom


Jonathan Rynd said:
Slight correction:

=IF(A1>0,A1&IF(AND(MOD(A1,100)>10,MOD(A1,100)<20),"th",CHOOSE(MOD(A1,10)+
1,"th","st","nd","rd","th","th","th","th","th","th")),"")
Click to expand...
 
I just changed it to

=A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",
CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th",
"th"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top