• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to apply superscript in a formula

Hi,

Can someone help me as to how should I superscript a number(Ordinal) in a formula. I know it can be done via custom format but is there a way to do it in a formula. I am driving a cell via a formula and want to apply superscript in the same formula. Have attached the excel file for example.
 

Attachments

  • Book1.xlsx
    17.8 KB · Views: 6
I managed with this formula:
=D6&IF(OR(MOD(D6,100)={11,12,13}),"ᵗʰ",LOOKUP(--RIGHT(D6),{0,"ᵗʰ";1,"ˢᵗ";2,"ⁿᵈ";3,"ʳᵈ";4,"ᵗʰ"}))
I got those superscripted characters at this site: https://qwerty.dev/superscript-generator/
and copied the results inbetween the quote marks in the formula. See column E of the attached.

76864

If that doesn't work for you, those are only unicode characters and can be generated within the formula. Unfortunately, LOOKUP didn't like those in {} array, so I used CHOOSE instead, but that leads to a long formula:
Code:
=D6&IF(OR(MOD(D6,100)={11,12,13}),UNICHAR(7511)&UNICHAR(688),CHOOSE(--RIGHT(D6,1)+1,UNICHAR(7511)&UNICHAR(688),UNICHAR(738)&UNICHAR(7511),UNICHAR(8319)&UNICHAR(7496),UNICHAR(691)&UNICHAR(7496),UNICHAR(7511)&UNICHAR(688),UNICHAR(7511)&UNICHAR(688),UNICHAR(7511)&UNICHAR(688),UNICHAR(7511)&UNICHAR(688),UNICHAR(7511)&UNICHAR(688),UNICHAR(7511)&UNICHAR(688)))
See column G of the attached. You may be able to find a shorter one.
 

Attachments

  • Chandoo47122.xlsx
    12.6 KB · Views: 3
Last edited:
select the "th","nd" etc and right click to custom format and go to font and check on superscript. Screenshot is attached
 

Attachments

  • Capture.PNG
    Capture.PNG
    19.3 KB · Views: 7
Back
Top