Is it possible to automatically convert one column to superscript while concatenating with another column in Excel?

I am trying to Concat 2 columns in Excel in such a way that one column is shown in superscripted form as shown below

Is it possible in Excel that when I add data in columns B and C, the result is shown in column D as shown in the snapshot attached so that I don't have to manually change the first part in column D in every row to superscript?

I tried changing the formatting of column B to superscript and then use concat formula but it changes it to normal formatting in the resultant column D.

Any help will be highly appreciated!

(Note: For the sake of simplicity, this answer is written assuming you have a version of Excel that supports switch and ifs, which wouldn't be the case in Excel 2010. However, it would be pretty straightforward to convert to standard ifs.). If you don't have the unichar function, the solution would get much more tricky.

Let's start at the end and work our way backwards. Let's say you had a table in Excel that mapped a number n to its superscript equivalent. Something like this:

Then you could do as something as easy as doing a VLOOKUP to concatenate the superscripted number with the text: =VLOOKUP([@Rank],tSuperscripts,2,FALSE)&" "&[@Text], which would give you this: ¹ Some String. (For the sake of simplicity, I didn't add in the parentheses; however, that would be trivial to do, as you'll see below).

So then the question becomes how to populate your "lookup table". Deriving the superscript for a number from 0 to 9 is easy enough: Handle the special cases of n in (1,2,3), and you can just reference Wikipedia to write a function using unichar. Something like this:

=UNICHAR(SWITCH([@n],1,HEX2DEC("b9"),2,HEX2DEC("b2"),3,HEX2DEC("b3"),HEX2DEC("2070")+[@n]))

For numbers ≥ 10, it gets a bet more complicated. I think your best bet at that point would be to have "helper" columns that split out the original number into constituent digits, derive their superscripts, and then concatenate everything back. To that end, you could do something like this (basically have columns to pull a specific digit, and then have another column to derive the superscript for that digit, then concatenate everything into 1 column). Note the use of --. Since LEFT and MID turn the digit into a string

Note that you can extend this logic pretty easily for numbers ≥ 100, 1,000, etc.

Once you have this table populated, you can use the vlookup shown at the top. As stated, if you need the parentheses (0x207d and 0x207e), you can add those in to the lookup table. For example: =CONCAT(UNICHAR(HEX2DEC("207d")),[@[n1_super]],[@[n2_super]],[@[n3_super]],UNICHAR(HEX2DEC("207e")))

You can use UNICHAR function to accomplish this. The user LAMBDA function SUPERSCRPT(x), does the conversion of the input string (x) considering each case (formula 1):

=LET(SUPERSCRPT, LAMBDA(x, LET(lk, HSTACK(
  VSTACK(""&SEQUENCE(10,,0),{"(";")"}),
  VSTACK({8304;185;178;179},SEQUENCE(6,,8308),{8317;8318})),
 y,MID(x,SEQUENCE(LEN(x)),1),
 REDUCE("",y,LAMBDA(ac,a,ac & UNICHAR(XLOOKUP(a,INDEX(lk,,1),INDEX(lk,,2))))))),
 BYROW(A2:B6, LAMBDA(x,SUPERSCRPT(INDEX(x,1))&INDEX(x,2))))

You can also define SUPERSCRPT as an array function, so you don't have to build the lookup table (lk) on every call and you can use it for a single cell or a single column array (formula 2):

=LET(SUPERSCRPT,LAMBDA(x, LET(lk, HSTACK(
  VSTACK(""&SEQUENCE(10,,0),{"(";")"}),
  VSTACK({8304;185;178;179},SEQUENCE(6,,8308),{8317;8318})),
  BYROW(x, LAMBDA(xi,REDUCE("",MID(xi,SEQUENCE(LEN(xi)),1),
   LAMBDA(ac,a, ac & UNICHAR(XLOOKUP(a,INDEX(lk,,1),INDEX(lk,,2))))))))),
  SUPERSCRPT(A2:A6)&B2:B6)

Here is the output:

What is does is first to build a lookup table (lk) you can have it outside of the formula in the Excel spreadsheet and reference it. This table has two columns: input (text data type), output (UNICODE decimal number). Here is the reference table:

For formula 1: The input (x) is split by rows (y) via MID function. We use REDUCE to do the recursion for each element (a) of y. On each iteration it finds the input value (a) in the lookup table (lk) via XLOOKUP and extracts the code number, then invokes UNICHAR function and concatenate the result to the accumulator (ac). We use BYROW to spill the array result for the input range.

I found the corresponding UNICODE values here: Unicode subscripts and superscripts, section: Superscripts and subscripts block. Numbers are represented in hexadecimal. For example the superscript 1 (¹) is represented by the hexadecimal number: 00B9. The lk table in the formula uses the equivalent decimal number, so basically I converted them via HEX2DEC Excel function. For example: =HEX2DEC("00B9") which returns 185.