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]

Yeah, I tried that, but it didn't like the ^{ combo. But I figured it out by putting in an arbitrary symbol to find and replace again. So glad I found a solution for all of this crap before tomorrow even if it isn't the most elegant or efficient solution.

But I could not have done that without you, so thank you so much for that. I don't think I will be going near VBA for a while though.

I'm going to try doing this from scratch in Python and then maybe Java for practice. Worried I looked stupid in front of my bosses and coworkers. I'm sure this isn't going to be the last time they ask for something like this.

 

[inactiveuserps07]

Technically, the way to insert the ^^ is to click where you want it, click "Special" at the bottom of the Find/Replace window, and select "caret ^" from the list.

 

[theflyingdutchdog]

But will that work with the brackets? Because that's what it seems to have a problem with. It doesn't like the combination of ^^ and {.

 

[inactiveuserps07]

It works for me.


Be sure you click where you want the ^ while the field is blank. Then click "Special..." at the bottom of the Replace window and choose "Caret character" from the list. It will insert what looks like ^^. From there you can TYPE {^&} as you normally would. Make sure your Format > Fontis cleared of all Effects. Then Click "OK."


Good luck today!

 

[theflyingdutchdog]

The answer was just to do all of this in Word and record the Macros. Omg, I didn't even know I could do that. If I knew, this would have been over *days* ago. But your tip about {^&} working is what made that possible, so thanks! My boss didn't know you could do that, but once he found out, he showed me how to record the macro in Word.

I feel like such an ***, but I'm relieved it got done.

 

[inactiveuserps07]

80% of what we worry about ... never happens. And 80% of the remainder won't matter in a year.

Glad you found a less hair-raising (or hair-losing) solution!