Forum Discussion
Convert numbers to ordinal numbers (1st, 2nd, 3rd, etc.)
I suggested to a client they should stop entering ordinals (text values) and use numbers instead to facilitate sorting and calculations. I wish Excel had a number format for this, but it doesn't. I c...
Thanks for these alternatives. The solution using MOD runs into problems for negative numbers. Here's a discussion of it in case you're interested - https://answers.microsoft.com/en-us/msoffice/forum/all/mod-for-negative-numbers/5a16dd87-558a-4067-ab48-309784ed0043
very true; easy solution to wrap the variable with ABS(). see correction above
that said there is not check or correction for decimal numbers. 0.1 or 1.2 or etc...
those could be 'pre-screened' using
=IF(ISNUMBER(pos_int)*(pos_int=INT(pos_int)), ... which would return the "--" from the end or could have its own nested IF and then return itself.