Multiple Replace over Range of Cells

[theflyingdutchdog]

So I'm tasked with taking one column in an Excel spreadsheet and converting those values into Latex (TeX) format and placing the formatted values in another column.

For example, if I have CS3# in column A3 then I need to replace that with CS3/hash in column B3. Some symbols may contain more than one character that needs replacing (AC/DC# would have to be replaced with AC/_DC/hash).

In addition (and this is the really hard part I'm leaving to last), I also have to detect subscripts and change them to regular font and put { } around it. So V(subscript)DD, then it would have to become V_{DD}. The subscript "DD" (pretend that's subscript) becomes regular "DD", and gets { } around it and an underscore in front. I may have to do this last part manually, but my boss is insisting I come up with some program to change most of the symbols.

Some system of error management would be great too.

I tried using this code from www.thespreadsheetguru.com, but it seems to be looping through my spreadsheet too many times, and therefore replacing symbols multiple times so that AC/DC# would become AC///underscoreDC///hash instead of the correct answer above. Also, I don't need the code to work on the whole workbook (not that it matters as I only have one worksheet). I just need it to apply to the whole worksheet.

Any help at all is greatly appreciated. I don't know any VBA, and this is urgently needed.

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long


fndList = Array("#", "_", "/")
rplcList = Array("/hash", "/underscore", "/_")


'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x


End Sub

 

[theflyingdutchdog]

The first column is not as important as making sure it gets translated to Latex form in the next column (like \overline{CAS}). They are going to put it in Schema for I don't know what.

 

[theflyingdutchdog]

Also, I have *no idea* how many of the symbols they will be giving me other than this worksheet will have overline symbols. They probably don't know either.

 

[inactiveuserps07]

Again, here, I'm lost when you say "... to get it in Schema." So the end goal remains unclear. What if you use formatting in Excel that isn't compatible with whatever Schema is?

And, if it makes you feel any better, I literally have 35 years of professional programming experience ... and I've never come up against this type of task. As you've learned from your online searches, it's not something people are doing.

For the overline, here is a suggestion:


Make sure your two data columns are aligned as Bottom Aligned + Center Aligned (Home tab > Alignment > rightmost and center icons, respectively). In addition, Tahoma is a good font for that column, because it is a bit taller than other fonts.


From there, say your entry is the reverse of \overline{CAS} into A2:

Enter into Excel three underscores, then Alt-Enter, then CAS.


To decode (in B2) that part of the process would be:

=IF(CODE(A2)=95,"\overline{"&RIGHT(A2,ROUNDDOWN(LEN(A2)/2,0))&"}",A2)


Might that work?

 

[theflyingdutchdog]

I'm not sure what you mean when you say to enter \overline{CAS} in reverse. Have you ever done anything like this in Java?

 

[inactiveuserps07]

"...enter \overline{CAS} in reverse" meant "Envision what the unformatted version would look like: CAS with a bar over the top."

In other words, you'd enter into the cell three underscores like this ___, hit ALT-ENTER, and then enter CAS, and it would look like this:

___
CAS


And I'm afraid I've never done anything "like this" at all before now. Making it up as I go.

 

[theflyingdutchdog]

Your telling me.... This is my first program at my internship. The first one. And they need it like *now* or really Friday, but still. And now I'm worried I'm looking incompetent for not being able to do this. Others have done this in Java.

 

[inactiveuserps07]

If others have done this, why are you inventing the wheel instead of just using their ready-made tools?

In any event, good luck. Life's too short to be stressed out this much.

 

[theflyingdutchdog]

I meant a friend had to do something similar at another job. This hasn't been done here.

But you are right. This is ********, lol. Thanks so much for all of your help. If I can get the subscripts/superscripts to stay the same, I can use your trick with Word--which was very useful btw, so thanks so much for that.

 

[theflyingdutchdog]

Hey is there a way to change the superscripts in Word so that the superscripts are ^{ }? I put in the ^{^&} like I did with subscripts and _{^&}, but it didn't work. =( =((((

 

[inactiveuserps07]

Because the ^ character already has specific meaning in Find/Replace, you have to escape it by entering it twice to mean "I want to actually put this symbol here and not just use it as an operator."

So:

^^{^&}