Subscript changes from cell to cell

Question

Hi,

I am working with formulas which require subscript. All cells show the subscripted characters. However when "viewing" different cells under formula column, some appear with the subscript while other do not and the formulas do not print correctly.  I have attached screen shots to try and give you a visual. Why does this happen?

Thank you.

Answer 1

Yashinon,

Almost certainly, the explanation lies in the difference between two font series : the “classical” one, where the proper numeric parts of a chemical formula are expressed as genuine subscripts (and generally even superscripts – remind ions) and new Unicode fonts (Arial Unicode or  Calibri), where subscript and superscripts are also autonomous characters. So in your second case, the notation in a cell is correct with proper subscripts that, however, are not inherently displayed in the scripting row.

In the first case, the record in the cell was created by means of subscript characters that, on the

contrary to the previous situation, stay as such when displayed; the last 2 is left in basic size. This method has a main advantage in the possibility of referencing the formulas without destroying the proper layout of subscripts. The main drawback is in very poor availability of subscripts,  and superscripts greater then 3. These characters are coded with number 8320 and followings, so picking them all up is difficult for an outsider. The reference of a chemical formula with formatted subscript, of course, doesn’t respect any formatting of individual characters, and so the reference cannot be formatted independently.

You can prove this explanation by checking up the different fonts in dissimilarly behaving cells.

All that labor can be largely facilitated by a series of user defined procedures (macros) hinted in the basic version by Rick.

Regards

PB

Answer 2

I am not 100% sure what you are wanting from us, but let me take a guess. The following macro will process the cells in Column B on the active sheet and change display of this...

C5H12N2O4S22HCL

to the same thing except that all the numbers will be subscripted.

Sub ChemicalFormat()

  Dim X As Long, Text As String, Cell As Range

  For Each Cell In Range("B1", Cells(Rows.Count, "B").End(xlUp))

    Text = Cell.Value

    If Text Like "*[A-Z]#*" Then

      For X = 1 To Len(Text)

        Cell.Characters(X, 1).Font.Subscript = Mid(Text, X, 1) Like "#"

      Next

    End If

  Next

End Sub