Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.