Subscript changes from cell to cell

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.

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

*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated February 27, 2018 Views 46 Applies to: