i wanna use concatenate formula on excel but also contained with squared symbol, but it's not work on excel. the formula is roughly like this:

=CONCATENATE("13";"²")

i want to get an output like this:

13²

i've tried to use superscript features on the font but it didn't work if the text is inside the concatenate.

i need to use concatenate because i want to make automatization.

is it possible to do with an excel 2019? or is it possible but also require VBA?

Thanks for the answer.

CodePudding user response：

Try =CONCATENATE("13";CHAR(178))

CodePudding user response：

I also found a way to make this work without using concatenate, it just requires making a table to map to superscript numbers.

Step 1: Characters Table

Somewhere else, build a simple table with a map from "Digit" numbers to "Superscript" numbers. In the first column, "Digit", just type something like:

-9

-8

-7

-6

-5

-4

-3

-2

-1

0

1

2

3

4

5

6

7

8

9

In the second column, "Superscript", go to Excel Ribbon Insert > Symbol. In the tab "Symbol", change the comboboxes "Font" to "(normal text)" and "Subset" to "Superscripts and Subscripts". Insert now each character in its corresponding cell be sure to use "Superscript Minus" for negative numbers. Superscripts 1, 2 and 3 are located in the subset "Latin-1 Supplement".

-9 ⁻⁹ -8 ⁻⁸ -7 ⁻⁷ -6 ⁻⁶ -5 ⁻⁵ -4 ⁻⁴ -3 ⁻³ -2 ⁻² -1 ⁻¹ 0 ⁰ 1 ¹ 2 ² 3 ³ 4 ⁴ 5 ⁵ 6 ⁶ 7 ⁷ 8 ⁸ 9 ⁹

And so on. Please notice that these are not "formatted" numbers, but special symbols with no numeric value.

Step 2: Vlook Function

Put the digit you want displayed normally in a column, set up like it is in 'A1' below, i.e.:

Cell A1: 13

Then, put the number you want to be displayed in superscript format, i.e.:

Cell B1: 3

Then, use this function to create the final output.

Cell C1: =A1&VLOOKUP(B1,Sheet2!$A$1:$B$20,2,FALSE)