Excel - Adding Superscript to Ordinal Numbers
If you use Word regularly, you probably know that
whenever you want to specify an ordinal number (i.e., first, second, third…)
all you have to do is add 2 letters to the cardinal number and Word automatically
converts it into an ordinal number: you type 1st and word changes it 1st , 2nd is transformed into 2nd etc.
But what about Excel?
Well, here the situation is much more complicated. In order to add these 2
superscript letters next to the number 1 (for example) you need 5 steps:
1.
Type “1st”
2.
Select “st”
3.
Press CTRL+1 (Format
Cells)
4.
Check the Superscript
checkbox
5.
Press the OK button
Pic 1: Adding superscript letters – the old way
Now, if you have many such ordinal numbers, this process might
take a few minutes.
I’ve developed an alternative method which might save you some time.
It involves 3 steps which can automate the process of
adding the superscript to the first 20 natural numbers with only one formula.
Step1:
Write the 6 formulae that appear in the picture. These are the 6 superscript
letters that we use in all the ordinal numbers: st (for: 1st ), nd
(for 2nd)
r (for 3rd) and h (for 4th,5th…..20th)
These formulae “convert” the Unicode of these letters to superscript
letters.
The formulae create 6 superscript letters: “s” (in M2), “t” (in M5), “n” (in
M8), “d” in M11, “r” (in M14) and “h” (in M17).
Pic 2: Creating the 6 necessary Superscript Letters (M2:M17)
Step
2:
Combine the 4 pairs of letters that we use in all the ordinal number:
(st
(for: 1st ), nd (for 2nd), rd (for 3rd) and th (for 4th,5th…..20th)
The result is shown in cells D1:D4
Pic 3: Creating 4 Pairs of Superscript Letters (D1:D4)
Step3:
Write the following Formula in cell A1 and drag it all the way down to cell A20:
=IF(ROW()=1,ROW()&$D$1,IF(ROW()=2,ROW()&$D$2,IF(ROW()=3,ROW()&$D$3,ROW()&$D$4)))
Pic 4: One Formula for the first 20 Ordinal Numbers
אין תגובות:
הוסף רשומת תגובה