Formula for PLACES not displaying the "TH" Places like 45th Place it comes out 45 Place

[RudeBoy]

In excel I have a formula displaying places
ie !st Place, 2nd Place 3rd Place etc

The formula is like this:
=D46&MID("thstndrdth",MIN(100,2*RIGHT(D46)*(MOD(D46-11,100)>2)+1),2)&" Place"

The problem I am encountering is that the "TH" places are not displaying like this:
45 Place
46 Place
47 Place
48 Place
49 Place

 

[Rick Rothstein]

In excel I have a formula displaying places
ie !st Place, 2nd Place 3rd Place etc

The formula is like this:
=D46&MID("thstndrdth",MIN(100,2*RIGHT(D46)*(MOD(D46-11,100)>2)+1),2)&" Place"

The problem I am encountering is that the "TH" places are not displaying like this:
45 Place
46 Place
47 Place
48 Place
49 Place

That method for finding the ordinal suffix does not look right to me (I use 9 in place of that first 100). Try it like this...

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

 

[RudeBoy]

That method for finding the ordinal suffix does not look right to me (I use 9 in place of that first 100). Try it like this...

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

That Worked Thanks..

Only fall out is st has a space in front
So every st place has a space like this EXCEPT 1st.

1st is fine then 21 st, 31 st all have spaces but that is no big deal Thanks

 

[Rick Rothstein]

That Worked Thanks..

Only fall out is st has a space in front
So every st place has a space like this EXCEPT 1st.

1st is fine then 21 st, 31 st all have spaces but that is no big deal Thanks

The formula I posted, as I posted it, has no space between the number and the ordinal suffix for any number in cell D46. Did you modify the formula I posted? If so, show us what you are now using.

 

[RudeBoy]

The formula I posted, as I posted it, has no space between the number and the ordinal suffix for any number in cell D46. Did you modify the formula I posted? If so, show us what you are now using.

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

Here is the code but maybe it just looks like a space
as that font is big. I did a copy & paste in notepad & I do not see a space so its just the font so we are good thanks