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]

I also have a significant amount of symbols that have bars over them like a (--) over an x or something. So for something like x bar where I want a line over the x, I have to replace that with \overline{x}.

So a symbol I have to change is CAS with a line of all the letters. To change this, I have to type \overline{CAS}.

 

[inactiveuserps07]

theflyingdutchdog ... whew ... I don't know quite what to tell you.

First, some of the exchanges you just mentioned don't match what you said in your original post (e.g., "AC/DC# would have to be replaced with AC/_DC/hash").

In addition, the Excel sheet sample you shared had symbols in multiple columns instead of in one list, etc., which makes it hard to write formulas. If the sheets really look like that, rather than being in one list, you'd most likely have to use some sort of script; there's no definite place to put the formula, even if one could be devised.

I looked at your "overline" entries in the sheet you sent, and they really aren't overline. If you use =LEN(G9), for instance, you'll see that it says that cell G9 is made up of 12 characters: three of them are the ASCII characters you'd expect, along with 9 characters that are simply listing as question marks. In reality, you've got non-printing elements forcing things on top of one another to look like overline, but Excel just sees them as "What the ...?!" and calls them question marks. There is no pattern to them, either. Cells G7 and G8, which appear longer, show a LEN() of 6 each; and the question marks show no pattern as to where they appear in G7, G8 or G9.

You see the issue. "Garbage in, garbage out." I'm not sure where this data was lifted from before it was put into Excel, but it's not pure. And that leaves you with a host of problems in trying to "decode" it.

Wish I had better news.

If you want to try to explain the fundamental purpose of what you're doing, from the ground up, maybe I or someone else here will be able to offer further suggestions.

 

[theflyingdutchdog]

Thank you for trying, and I know what you mean about G7, G8, and G9. I was having trouble inputting the bars over the symbols. I may have to redo them for consistency. How did you find the length of the cell? Did you just enter the formula LEN() for the cell?

I have a program that will replace all of the symbols, and I know how to do the subscripts in Word, so I'm hoping I can do the subscripts, superscripts, and bars in Word, copy and paste them, and then do the rest in VBA.

Excuse the inconsistency with the symbol legend. I went back and looked at it, and it turned out that I was wrong about the forward slash /. For that, I just leave it alone.

But here is the code I have:

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


It doesn't put them in columns adjacent to the original, but I'm not sure how important that is. I will ask. It places them in a new sheet in the same workbook. Thanks again! You are awesome!

What I am trying to do is take the symbols I am given and translate all of them into Latex (TeX) format. It's a format used in math and computer science. I have hundreds of symbols to format, so I will need a program to speed up the process.

 

[inactiveuserps07]

So is the sheet you sent me just a "test" sheet? Will the final all be in one list?

Looks like you are further along than you were, at least. You can use the same Word process for superscripts.

I have no idea what's going on with the overlines!

 

[theflyingdutchdog]

Well, I'm further along thanks to Reddit and ppl like you, so thanks. Also, I'm learning a lot in the process which is great as I need to get the most out of this internship.

I thought I would do the same thing with the bars? You know, I have a lot of symbols that have bars over them, and I have to format them in a very similar way as to the superscripts and subscripts. =/
That's a big problem.

Yeah, this is just a small sample spreadsheet. I am going to be getting a similar spreadsheet with many more symbols soon, so I have to get this working.

I also don't know if they will be okay with me altering them in Word as well. I also have no error handling, so that's an issue probably. Also, it's placing the replacement symbols in a new spreadsheet instead of in the columns adjacent to the original, and I'm not sure if that's okay or not. It's definitely not as efficient to copy and paste the columns back to the original worksheet. But this is a lot more than I had yesterday....

 

[inactiveuserps07]

This is one of those things that Chip and Dan Heath (Made to Stick) call "the curse of knowledge": you know exactly what you're trying to do, including the end goal (e.g., why you're doing it, how it's making some process better/faster, etc.), so it feels like it's all clear as you're talking about it, because it's clear to you. But for us "out here" ... not so much.

If I were sitting beside you and could see all the pieces, I'd likely be able to offer more toward a solution that works. But, as powerful as words can be, I don't know if they can replace actual first-hand observation.

Is this a project you were tasked with? Or is it something you're offering to do, to make yourself valuable, etc.?

If you can explain from the start, some new light may be shed. What were people doing before this idea? What was the workflow (e.g., they got X and they needed to do Y with it because ...)? Where will the initial data be coming from (e.g., lifted from the web somewhere, copied and pasted from digital documents, typed in by hand before conversion, etc.)? What is the final form of the data once converted (e.g., copied and pasted to form a manual of some sort, used as a legend for some other program's reference, etc.)? Who will use the converted data? For what?

You see, there is a lot to know, and it all effects the big picture of how to go about a solution. Anything more you can tell us may lead to one more answer than you had before.

 

[theflyingdutchdog]

So is there a way to detect a bar symbol or overline symbol (unicode 0305) and replace it the same way you told me to do the subscript and superscript? I really need to know ASAP.

Thanks so much for all of your help! You are so awesome. I am googling in the meantime.

 

[theflyingdutchdog]

Hey, so no one was doing this before me as far as I know, I am typing these symbols in from tables in pdf's for electronic components, and I am not volunteering to do this...at all. I am told I must do this.

I type the symbols in from the pdf's into an Excel spreadsheet. I then create an empty column next to the columns of original symbols. There, I need to have the Latex format of the original symbols.

 

[inactiveuserps07]

So, your boss told you, "If you, as the new guy, don't figure out how to do something we ourselves couldn't figure out before you arrived — you're fired!"?

Time to find a new job, my friend.

I hate to think that you're entire employment rides on whether you and the free help online can do something they've never yet done themselves.

That said ...

Is the END GOAL of all of this simply to provide a reference to others? or is it hoped that this "translation" be directly used by another program to "interpret" other data afterward?

And roughly how many overline entries do you anticipate?

Does it just need to look like overline characters in that first column?

 

[theflyingdutchdog]

Lol, well they didn't say I would be "fired", and I'm not sure that they couldn't figure this out themselves. It's just that I'm an intern, and they are probably giving me what they consider their easy and/or tedious stuff. But I have never done anything like this before. And they have given me a limited time-frame.

So when they get the symbols and give them to me, they have 6 days to get it in Schema, which means I have 2 days to format them all with no mistakes (little mistakes mean a completely different symbol). I don't have any error handling in my code above either, but oh well.

I don't think any other job will be different. They will probably all ask something like this of me. I wont be fired, but it will look very, very bad. I'm supposed to have some programming skills.