Tranferring chemical formulas with subscripts

Hi All,

To transfer data (a chemical formula with subscripts) to another sheet, I am using =Sheet1!G9 to transfer a chemical equation from cell G9 in Sheet1 to a cell in Sheet2. Everything works fine except that the subscripted items are no longer subscripts. Is there a different equation I need to use or is there some setting to go to in my Excel 2003?

Over the years when I have tried to do this, I have not found a way to do what you want to do. The subscript is formatting applied to a specific character within a cell containing a text string. A cell formula like you want to use can only carry the cell "value" but cannot carry over the formatting associated with the cell.

For me, this isn't a problem most of the time. C3H8 or C₃H₈ are both effective at communicating "propane." The only time I get to worked up over the subscripts is when the table is going to end up as part of an official report. In those cases, I create the string literal, then copy and paste it across the tables, because there isn't a ready way to link them together with worksheet formulas.

I'm sure it would be possible to create a VBA sub procedure that could automate the same thing, but for what I do, it would take longer and more work to write the procedure than to simply make the text string and copy it.

Perhaps you could use a macro like this?

Sub Chem_Format()

    Dim cell        As Range
    Dim s           As String
    Dim i           As Long

    For Each cell In Selection
        s = cell.Text
        For i = 1 To Len(s) - 1
            If Mid(s, i, 2) Like "[A-Za-z)]-" Then cell.Characters(i + 1, 1).Font.Superscript = True
            If Mid(s, i, 2) Like "[A-Za-z)]+" Then cell.Characters(i + 1, 1).Font.Superscript = True
            If Mid(s, i, 2) Like "[A-Za-z)]#" Then cell.Characters(i + 1, 1).Font.Subscript = True
            If Mid(s, i, 3) Like "[A-Za-z)]#-" Then cell.Characters(i + 1, 2).Font.Superscript = True
            If Mid(s, i, 3) Like "[A-Za-z)]#+" Then cell.Characters(i + 1, 2).Font.Superscript = True
            If Mid(s, i, 4) Like "[A-Za-z)]##-" Then cell.Characters(i + 2, 2).Font.Superscript = True
            If Mid(s, i, 4) Like "[A-Za-z)]##+" Then cell.Characters(i + 2, 2).Font.Superscript = True
            If Mid(s, i, 2) Like "[Mm]#" Then cell.Characters(i + 1, 1).Font.Superscript = True
        Next i
    Next cell
End Sub

Select the range you whish to "transfer" and run macro. The only problem with this macro is if the charge is + or - one unit and the number of molecules before the + or - is equal or greater than 2 it will transfer molecule number to charge number. One workaround is writing NO3- as NO31- and edit.

Don't remember the original author of this macro. I think snb or shg wrote it and I've extended it to transfer more complex formulas.

Alf

Thanks all for your replies. I was concerned that this would be the case. Formatting manually isn't a big task for me. I just thought with Excel's being such a great program, there would be a simple way to do this.

John