3

I have an XL sheet with chemical formulae - which is basically just letters with numeric subscripts and superscripts. I can save as an html page and get the <sub> and <sup> html elements, but they are burried under lots of other markup and css.

Is there a way to simply get the formula for a cell with H2O as markup like this: 

H<sub>2</sub>O
Lance Roberts
  • 8,837
Graeme
  • 269

2 Answers2

3

If you wish to convert subcript/superscript text to corresponding HTML markup, create a VBA function(press ALT+F11 to go to the editor) with this code:

Function SubToHtml(rng As Range)

Dim c, ns As String

For i = 1 To Len(rng.Value) Step 1
    c = rng.Characters(i, 1).Text
    If rng.Characters(i, 1).Font.Subscript Then
        ns = ns & "<sub>" & c & "</sub>"
    ElseIf rng.Characters(i, 1).Font.Superscript Then
        ns = ns & "<sup>" & c & "</sup>"
    Else
        ns = ns & c
   End If
Next i

'to take care of adjacent sub/superscript characters
ns = Replace(ns, "</sub><sub>", "")
ns = Replace(ns, "</sup><sup>", "")

SubToHtml = ns

End Function

Usage

Assuming A1 contains the string C2O3. Enter this formula into a blank cell:

=subtohtml(A1)

It should show this in the cell as a result: 

C<sub>2</sub>O<sup>3</sup>

Replace the function name if you wish. I couldn't think of a fancier one. :D AFAIK, there isn't a solution that doesn't involve VBA; since you would have to go through each character in the cell and check its case or font format.

Edit: I assumed your texts won't contain any adjacent sub/superscript characters. Just in case they do, I inserted some code that would take care of strings that have adjacent sub/superscript characters, such as CO20.

Ellesa
  • 11,185
1

Go to: Layout > Cell properties > Font type

You should check the "Subscript" box.

Source:

http://www.helpmij.nl/forum/showthread.php/221389-Subscript-in-Excel

Gareth
  • 19,080
RobinJ
  • 1,020