# Detecting Super/Subscripts

#### theflyingdutchdog

##### New Member
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)

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Akuini

##### Well-known Member
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
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
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)

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
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
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
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
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
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
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.

Replies
17
Views
6K
Replies
35
Views
3K
Replies
0
Views
434
Replies
13
Views
3K
L
Replies
4
Views
792
Legacy 223018
L

1,171,203
Messages
5,874,332
Members
433,044
Latest member
drewbizzy

### 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.

### Which adblocker are you using?

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

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