Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer
    
That looks exactly what I need. Thanks! – Graeme May 30 '11 at 18:29

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

share|improve this answer
    
Yes, but how do I then save that in the form H<sub>2</sub>O for importing into a database? – Graeme May 30 '11 at 17:59
    
A database? Isn't that Access in stead of Excel? In that case: No idea how to do it. – RobinJ May 30 '11 at 18:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .