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

 

[inactiveuserps07]

It's tough to help you without knowing all of the parameters. YOU know what you're looking at, but we don't. So theoretical examples aren't as helpful as one might hope.

For instance, you said that "V(subscript)DD ... would have to become V_{DD}...," but the parameters from out here are not clear.

Are all subscripts numbers? Or are there letter subscripts?

Are all subscripts two characters long?

Every detail matters.

If you can provide full specs and parameters, you'll up your chances of someone here being able to help you.

 

[theflyingdutchdog]

Thanks for the feedback. How would I provide access to the actual spreadsheet? That might help quickly show what I am trying to accomplish.

The subscripts are mostly letters, but can presumably be numbers (as can superscripts), and they are of variable length. Usually they are 1-3 letter subscripts, but this isn't always the case. It just varies so much. And sometimes (fairly often) the subscripts are 3 letter with some ( ) such as R(RMS). That would need a {R(RMS)}. So internal formatting in the subscript has to be maintained.

 

[inactiveuserps07]

I'm sure someone has a fancy way of doing this. If it were me, I'd go for quick-and-easy:

1. Select your entire range of data in Column A and copy it to your clipboard (Ctrl-C)

2. Open Word and past it into a new document (Ctrl-V)

3. In word, hit Ctrl-H to open the Replace dialog.

4. Click in the "Find What:" box once to place the cursor there. Don't type anything. Click the [Format...] button at the bottom. Choose "Font..." from the list. In that dialog, under "Effects," make sure "Subscript" has a check mark next to it and that all other boxes in that section are empty (you may have to double-click them to turn them off). Click "OK" to return to the main Replace dialog.

5. Click in the "Replace With:" box and enter the following: _{^&} The ^& just means "whatever this is." Click the [Format...] button at the bottom. Choose "Font..." from the list. In that dialog, under "Effects," make sure ALL boxes are empty (you may have to double-click them to turn them off). Click "OK" to return to the main Replace dialog.

6. Click "Replace All." Your data should now have all subscripts formatted as you wish.

7. MANUALLY select the entire list of data in Word (don't use Ctrl-A, because it may pick up superfluous non-printing characters). Hit Ctrl-C to copy to the clipboard.

8. Go back over to Excel. Your range should still be selected. If not, just select it again. Hit Ctrl-V to paste the formatted data into the column.

 

[theflyingdutchdog]

Omg, that is genius! Thank you! I don't see why I can't do all of the symbols this way for all of the changes, but they want a program, are insisting on a program, perhaps because there are *so* many symbols I will be getting (something like 500). But this should at least take care of the subscripts.

Let me attach a link to the dropbox in which the original spreadsheet is stored. That might help as well.

Now I just have to figure out how to iterate through the rows and change the symbols and replace them in column B.

A friend suggested doing this in Java instead of VBA since I am *much* less familiar with VBA (although I'm not that familiar with Java either, but much more so). What do you think about that?

Thanks again!

 

[theflyingdutchdog]

Here is the link to my original spreadsheet in dropbox: https://www.dropbox.com/s/794sa85k6i9mga0/SymbolCompareJESD79.xlsx?dl=0

 

[inactiveuserps07]

In your sheet, it appears that the ONLY subscripts appear after a single letter "V." Will this always be the case? If not, is there a limited set of letters or combinations after which subscripts will ever be found?

 

[theflyingdutchdog]

No, I wish. Unfortunately there's no way to know what will have a subscript. I will be getting spreadsheets with hundreds of symbols (about 500 they are estimating now), and they don't know that there's a pattern with the subscripts. I have seen other symbols that aren't preceded by a V that have subscripts like I. There's no set way to know. But the Word process worked great!

It may not be ideal, but maybe I can do that part in Word, and then I still have to figure out how to automate the other changes like "#" becoming "/hash", etc.

Thanks so much for helping!

 

[inactiveuserps07]

Everything but the subscripts should be able to be accomplished with one helper column utilizing nested REPLACE calls. If you want to provide your entire conversion legend, I'll see what I can do.

 

[theflyingdutchdog]

I don't have the entire legend because I haven't gotten all of the possibilities for the symbols yet, but I can give you what I do have, which is most of what I need it to do.

superscripts must become: ^{ } Example: x^{2}
greek letters/symbols like delta become: \Delta for uppercase and \delta for lowercase \alpha for alpha symbol and \infty for infinity symbol Example: \Delta_{VZ(temp)} is greek symbol delta, subscript VZ(temp)
underscores must become: \_ Example: A_B_C becomes A\_B\_C

hash (#) signs must become: \# Example: CK# becomes CK\#

backslash (\) becomes: \backslash Example: AC\DC becomes AC\backslashDC

subscripts become: _{ } Example: H_{2}O or if the subscript is R(MES) then it becomes P_{R(MES)}


Thanks so much! I'm looking up how to do this in Java in case I can't get this to work in VBA. Really worried as this is my first coding assignment at my first job, and I don't want to mess this up.

Here's a link to the Latex symbols in case the above is unclear: Art of Problem Solving .