Number Suffix

[gareth001]

Hi,

Is there anyway to automatically display the suffix to numbers, like the "th", "rd" and st without using an IF statement?

Thanks for your help in advanced

Gareth

 

[BobStubbs]

In which Office product/s do you want to do this?

Using Word 2000, you can set up an AutoCorrect rule to replace (for example) 3 with 3rd. This is 'intelligent' enough not to change a '3' within, or at the end of, a larger number:

3 becomes 3rd
123, 321 are not changed

This 'rule' is then shared with Excel and PowerPoint, so typing '3' into a cell or text box will cause '3rd' to be displayed.

To try this, choose the Tools ... AutoCorrect menu option in Word. Make sure the 'Replace text as you type' check box is ticked, and then add rules for the digits 1,2,3 etc. as required.

I hope that this helps.

Bob Stubbs

 

[gareth001]

This is for excel, i'm wanting to do this for one cell only, which is referencing another cell which is storing the number without any suffix.

 

[GlennUK]

Have a look at Chip Pearsons solutions ...

http://www.cpearson.com/excel/ordinal.htm

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BobStubbs]

You could use a VLOOKUP structure for this in Excel:

-- create a named range called Ordinals, containing two columns, laid out like this:

1 1st
2 2nd
3 3rd
etc

Example of the VLOOKUP function which would convert the value in cell A1 to its ordinal:

=VLOOKUP(A1,Ordinals,2,FALSE)

This will return the value #N/A for any number not found in the lookup table.

If you want the formula to return ordinals for values in the named range, and just repeat the original number if it is not in the Ordinals table, use this formula:

=IF(ISNA(VLOOKUP(A1,Ordinals,2,FALSE)),A1,VLOOKUP(A1,Ordinals,2,FALSE))

I hope that this is useful.

Bob Stubbs

 

[earthandfire]

A slight modification to BobStubbs suggestion:

Create a 2 column range named Ordinals - but containing just 4 rows:

0 th
1 st
2 nd
3 rd

and then use:

=A1&IF(ISNA(VLOOKUP(RIGHT(A1,1),Ordinals,2,False)),"th",VLOOKUP(RIGHT(A1,1),Ordinals,2,False))

Hope this helps.

 

[gareth001]

Thanks for all your help, after looking at all the options, I've gone for Chip Pearsons solution which uses a Choose function with mods.