Detecting Super/Subscripts

[theflyingdutchdog]

So I have symbols that I have to convert to LaTeX values. I thought I would be able to run my program for replacement and then use Word to format the super/subscripts using _{^&}. I know that isn't elegant, but I need this by Friday and it would work.

Well, when I ran my program, it changed the subscripts back to normal font.

Is there a way to keep the formatting of my original text (keep it in subscript form) after running my Replace functions program?

Otherwise, is there anyway to program VBA to recognize sub/superscripts and allow me to black _{ } and ^{ } around them?

Thank you so much for any help.

Here is the program I have now:

Sub Latexualize()

Dim rng As Range
Dim cell As Range
Dim txt As String
Dim wsOut As Worksheet

Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
Set wsOut = ActiveWorkbook.Sheets.Add(Type:=xlWorksheet)

For Each cell In rng
txt = cell.Value

' Add Replace() steps below, as needed
txt = Replace(txt, "", "\backslash")
txt = Replace(txt, "_", "\_")
txt = Replace(txt, "#", "\#")
txt = Replace(txt, ChrW(916), "\Delta")
txt = Replace(txt, ChrW(&H221E), "\infty")
' Output to new worksheet
wsOut.Cells(cell.Row, cell.Column).Value = txt
Next

End Sub

 

[Akuini]

So I have symbols that I have to convert to LaTeX values. I thought I would be able to run my program for replacement and then use Word to format the super/subscripts using _{^&}. I know that isn't elegant, but I need this by Friday and it would work.

Well, when I ran my program, it changed the subscripts back to normal font.

Is there a way to keep the formatting of my original text (keep it in subscript form) after running my Replace functions program?

Otherwise, is there anyway to program VBA to recognize sub/superscripts and allow me to black _{ } and ^{ } around them?

Thank you so much for any help.

Hi, theflyingdutchdog
I’m not sure what you want.
Do you want, for example:
HH^AA become HH^{^{AA}}
Or
HH^AA become HH^{AA}

 

[Akuini]

I'm assuming:
-The superscript/subscript part, if exist, is always in the end of a cell.
-You want: HH^AA become HH^{^{AA}}

Here's one way to do it:

Sub a1008755b()

Dim r As Range
Dim str1 As String, str2 As String
Dim j As Long, k As Long

Application.ScreenUpdating = False
For Each r In Range("A1:A4")
        j = Len(r)
        For k = 1 To j
          If r.Characters(k, 1).Font.subscript = True Then
             str1 = Mid(r.Value, 1, k - 1)
             str2 = Mid(r.Value, k, j - Len(str1))
             r.Value = str1 & "_{" & str2 & "}"
             r.Characters(k, j - k + 4).Font.subscript = True
             Exit For
          End If
          
          If r.Characters(k, 1).Font.Superscript = True Then
             str1 = Mid(r.Value, 1, k - 1)
             str2 = Mid(r.Value, k, j - Len(str1))
             r.Value = str1 & "^{" & str2 & "}"
             r.Characters(k, j - k + 4).Font.Superscript = True
             Exit For
          End If
        Next k
Next

Application.ScreenUpdating = False
End Sub

 

[Rick Rothstein]

So I have symbols that I have to convert to LaTeX values. I thought I would be able to run my program for replacement and then use Word to format the super/subscripts using _{^&}. I know that isn't elegant, but I need this by Friday and it would work.

Well, when I ran my program, it changed the subscripts back to normal font.

Is there a way to keep the formatting of my original text (keep it in subscript form) after running my Replace functions program?

Otherwise, is there anyway to program VBA to recognize sub/superscripts and allow me to black _{ } and ^{ } around them?

Thank you so much for any help.

Here is the program I have now:

Sub Latexualize()

Dim rng As Range
Dim cell As Range
Dim txt As String
Dim wsOut As Worksheet

Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
Set wsOut = ActiveWorkbook.Sheets.Add(Type:=xlWorksheet)

For Each cell In rng
txt = cell.Value

' Add Replace() steps below, as needed
txt = Replace(txt, "", "\backslash")
txt = Replace(txt, "_", "\_")
txt = Replace(txt, "#", "\#")
txt = Replace(txt, ChrW(916), "\Delta")
txt = Replace(txt, ChrW(&H221E), "\infty")
' Output to new worksheet
wsOut.Cells(cell.Row, cell.Column).Value = txt
Next

End Sub

Excel does not store the individual character formatting in the text string itself, rather, it stores them in the cell itself. That means if you want to format a portion of the text differently from the rest of the text, you must do so using the cells Characters property. I think, because of this, it may defeat what you say you are intending to do. Once applied, you would have to iterate each character via the cell's Characters property one at a time and see what formatting it has and then insert your meta-characters that would be used in LATEX to do whatever it is that LATEX does. This won't be a fast process at all. Excel is not a word processor, so word processing functionality is not even close to one of its strengths. I'm sorry to give you that bad news, but that is how I understand Excel's character formatting. If one of the other volunteers know differently, I am sure they will jump in and correct any misunderstandings I might have about this.

 

[theflyingdutchdog]

Unfortunately I can't assume that it will always be at the end of a cell, and the subscripts and superscripts are of variable length. Subscripts and superscripts can be numbers, letters, or even Greek alphabet (delta, alpha, etc). I used H2O as an example because everyone knows it. So H2O would have to become H_{2}O. And x^2 (x squared) would have to become x^{2}.

I also have other formatting that needs to be included in the program like CK_n would have to become CK\_n and CK# would have to become CK\#. Backslash "" would have to become \backslash, but only if it was included in the original symbol. I may be able to skip the \backslash, bc I'm not sure that the symbols will have backslashes at all.

The problem is, if I change the super and subscripts first, then extra formatting is added because of the underscores in front of the subscripts. So H_{2}O would incorrectly become H\_{2}O.

So how would I incorporate those other changes into the program you sent? How would I get around that?

Here are links to example spreadsheets in my dropbox. The first is a "before latex" original spreadsheet example: https://www.dropbox.com/s/ziqgh9962jyckg3/SymbolCompareJESD79(BeforeLatex).xlsx?dl=0

Here is an example of the formatted "after latex" spreadsheet with formatted symbols starting in Column B. Column B contains the Latex-formatted symbols of Column A and Column D has the Latex-formatted symbols of Column C and so on:

https://www.dropbox.com/s/2y93nh8dbxfwpyi/SymbolCompareJESD79(Latex2).xlsx?dl=0

This is due by tomorrow. I have tried using VBA for a week in vain. I'm afraid I chose the wrong language to do this in, and now I have no time. This is my first programming assignment at my first programming job, and I'm afraid I will look incompetent now.

Any help is greatly appreciated.

Thank you.

 

[theflyingdutchdog]

Rick, I'm afraid you are probably right, and I should never have tried to do this in VBA. Now I am running out of time.

Do you have another way of doing this that you would suggest? Python or Java?

Thank you for any help.

 

[theflyingdutchdog]

So there's no way to use the .Characters property to maintain the original formatting after running my VBA program? Because if I can maintain the original subscripts/superscripts after the replacements, I can format the superscripts and subscripts in Word.

Thanks.

 

[Rick Rothstein]

So there's no way to use the .Characters property to maintain the original formatting after running my VBA program?

I am not completely sure of what you are asking here, but the .Characters property can be used to both read and set the formatting properties of individual characters for the text within a cell.

 

[theflyingdutchdog]

So then why can't it be used in a loop to keep the formatting the same or detect the formatting and place { } around it?

 

[Rick Rothstein]

So then why can't it be used in a loop to keep the formatting the same or detect the formatting and place { } around it?

If you place what will be used as meta-characters around parts of the text, they will become part of the visible text if placed in a cell. In order to do what I think you are suggesting, you would need to hold the text with the meta-characters in them separate from the text you place in the cell and write a sophisticated interpreter to move between the two.