| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format cells to display ordinal numbers ex 21st
I would like to format numbers so that they appear as ordinals
ex 1 1st 2 2nd 3 3rd 4 4th etc. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format cells to display ordinal numbers ex 21st
You need to use a helper cell with a formula or a VBA procedure.
Here's a formula: A1 = 301 =A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"})) Returns: 301st Biff "Prince of Tama" <Prince of wrote in message ... I would like to format numbers so that they appear as ordinals ex 1 1st 2 2nd 3 3rd 4 4th etc. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format cells to only accept numbers | Excel Discussion (Misc queries) | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| How do I sum numbers in rolling format? (4 qtrs=4 cells to left) | Excel Discussion (Misc queries) | |||
| Numbers -format cells | Excel Worksheet Functions | |||
| Format cells to display truncated values? | Excel Discussion (Misc queries) | |||
Linear Mode
