Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Superscript Formula in Excel Function CONCATENATE

  1. Mar 9, 2015 #1
    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 : 12C (α,p) 15N

    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
    Code (Text):
    =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.
     
  2. jcsd
  3. Mar 9, 2015 #2

    mfb

    User Avatar
    2016 Award

    Staff: Mentor

    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&...
     
  4. Mar 9, 2015 #3
    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)
     
  5. Mar 9, 2015 #4

    pasmith

    User Avatar
    Homework Helper

  6. Mar 9, 2015 #5
    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 :
    Code (Text):

    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.
     
  7. Mar 9, 2015 #6

    mfb

    User Avatar
    2016 Award

    Staff: Mentor

    The "end" of superscripts seems to be a bit messy, so I would make them one by one:
    Code (Text):
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Superscript Formula in Excel Function CONCATENATE
  1. Excel - (Replies: 2)

  2. Matlab concatenation (Replies: 2)

Loading...