Mike,

Can you pl. explain how it works?

Thanks,

Sheeloo

Hi,

I first saw this posted by; I think, Rick Rothstein, and was fascinated by the way it works because it on the one hand looks complicated but when deciphered is brilliantly simple.

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

We are trying to return MID("thstndrdth",n,2)

Where n is either 1,3,5,7 or 9 to give us th,st,nd etc

The 1,3,5,7 & 9 are determined by the last 2 digits of the number in A1 as follows.

First this bit of the formula 2*RIGHT(A1) multiplies the last digit of A1 by 2 and will return an even number in the range 0 to 18

0,2,4,6,8,10,12,14,16,18

We then take the last 2 digits of the number, even if there aren't 2 it doesn't matter, and apply this formula

MOD(A1-11,100)>2

This to me is the clever part where11 is subtracted from the number and then take MOD 100 of that result and test if the number is greater than 2 returning either TRUE or FALSE

The table below shows the results of those 2 formula for the numbers 1 to 20.

Columns 2 and 3 are the 2 formula discussed above.

Column 4 is the 2 formula multiplied together

Column 5 is the reult +1

Column 6 is the formula MIN(9, result of column 5)

So as you can see the result of

MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1)

Is always 1,3,5,7 or 9 which is used in the MID formula to return the string with the 1,3,5,7,9 being of course determined by the number in A1.

1 |
TRUE |
2 |
2 |
3 |
3 |

2 |
TRUE |
4 |
4 |
5 |
5 |

3 |
TRUE |
6 |
6 |
7 |
7 |

4 |
TRUE |
8 |
8 |
9 |
9 |

5 |
TRUE |
10 |
10 |
11 |
9 |

6 |
TRUE |
12 |
12 |
13 |
9 |

7 |
TRUE |
14 |
14 |
15 |
9 |

8 |
TRUE |
16 |
16 |
17 |
9 |

9 |
TRUE |
18 |
18 |
19 |
9 |

10 |
TRUE |
0 |
0 |
1 |
1 |

11 |
FALSE |
2 |
0 |
1 |
1 |

12 |
FALSE |
4 |
0 |
1 |
1 |

13 |
FALSE |
6 |
0 |
1 |
1 |

14 |
TRUE |
8 |
8 |
9 |
9 |

15 |
TRUE |
10 |
10 |
11 |
9 |

16 |
TRUE |
12 |
12 |
13 |
9 |

17 |
TRUE |
14 |
14 |
15 |
9 |

18 |
TRUE |
16 |
16 |
17 |
9 |

19 |
TRUE |
18 |
18 |
19 |
9 |

20 |
TRUE |
0 |
0 |
1 |
1 |