Superscript Formula in Excel Function CONCATENATE

[parazit]

Hello Master.

I would like to ask a question about MS Excel and one of it's functions; CONCATENATE.

I'm trying to write a huge number of reactions in the Excel as the following form : ¹²C (α,p) ¹⁵N

So, since I have tons of reactions to organize according to the alphabetic order and isotopes I decided to use excel cells as following. (in parentheses, the values of upper reaction)

A1 : Target isotopes mass number ( 12 )
B1 : Target symol ( C )
C1 : Incoming particle ( α )
D1 : Outgoing particle ( p )
E1 : Product isotopes mass number ( 15 )
F1 : Product symbol ( N )

and used

=CONCATENATE(A1;B1;"(";C1;",";D1;")";E1;F1)

to write them in the form.

However, this is not true since I need that, cells A1 and E1 have to be written as the superscripts of cells B1 and F1 respectively in the same line.

Are there any way to do that ? I am open to any suggestion either solving this problem or a new, different way to let me write that tones of reactions in a suitable order.

Thank you,
Best.

 

[mfb]

I'm not even sure if combining superscripts and normal font size in the same cell is possible at all. You could use multiple columns for your formula, the first and third with superscript and the others with normal font.

By the way: instead of CONCATENATE, you can also use the shorter =A1&B1&...

 

[parazit]

I'm not even sure if combining superscripts and normal font size in the same cell is possible at all. You could use multiple columns for your formula, the first and third with superscript and the others with normal font.

By the way: instead of CONCATENATE, you can also use the shorter =A1&B1&...

Thanks for the advise about the shorter way. I'm aware of that but I think if I use concatenate, I'll be able to make superscripts more easily, which I won't be able.

About multiple cells idea; it seems it's not going to work since in that way, the reaction equation will be parted and I won't be able to order all the reactions. (or am I wrong? I really do not know)

 

[pasmith]

It can be done using VBA (in Excel 2003 at least).

 

[parazit]

It can be done using VBA (in Excel 2003 at least).

Dear pasmith, thank you so much. It gives me an inspiration. I also found that on the web.

Yes, it was done before using VBA but not exactly in the way that I need and suitable for reaction indication.

The code that I found is like that :

Sub test()
Range("C1").ClearContents
Range("C1").Value = Range("A1").Value & Range("B1").Value
For i = 1 To Len(Range("A1").Value)
    If Range("A1").Characters(i, 1).Font.Bold = True Then Range("C1").Characters(i, 1).Font.Superscript = True
Next i
For j = 1 To Len(Range("B1").Value)
If Range("B1").Characters(j, 1).Font.Superscript = True Then Range("C1").Characters(j + Len(Range("A1").Value), 1).Font.Superscript = True
Next j
End Sub

in where the value of cell A2 becomes a right superscript of the value in cell A1 and they all written to cell A3.

However, the reaction indication is not like that. So, can somebody please help me to re-arrange the code according to the reaction form.

Please do not withhold your ideas and knowledge.

Best.

 

[mfb]

The "end" of superscripts seems to be a bit messy, so I would make them one by one:

For i = 1 To Len(Range("G1").Value)
    If i <= Len(Range("A1").Value) Or (i >= lengthABCD And i < lengthABCD + Len(Range("E1").Value)) Then
        Range("G1").Characters(i, 1).Font.Superscript = True
    Else
        Range("G1").Characters(i, 1).Font.Superscript = False
    End If
Next i

Where lengthABCD is the sum of the length of the first four cells which has to be calculated first (and G1 has to be filled first, too).

You'll also have to generalize this to work with every row instead of just the first one.