Detecting Super/Subscripts

theflyingdutchdog

New Member
Joined
Jun 2, 2017
Messages
39
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 Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
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:
HHAA become HH^{AA}
Or
HHAA become HH^{AA}
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
I'm assuming:
-The superscript/subscript part, if exist, is always in the end of a cell.
-You want: HHAA become HH^{AA}

Here's one way to do it:
Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,163
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 2, 2017
Messages
39

ADVERTISEMENT

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

New Member
Joined
Jun 2, 2017
Messages
39
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

New Member
Joined
Jun 2, 2017
Messages
39

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,163
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Last edited:

theflyingdutchdog

New Member
Joined
Jun 2, 2017
Messages
39
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,163
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,776
Messages
5,677,666
Members
419,711
Latest member
dacrmcvega0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top