Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Nebel: Sep 30, 2002; Soiled Meat

For my job I'm taking a bunch of words written in a phonetic alphabet, and converting them to another phonetic alphabet. Essentially this is the same as transliteration.

First, I looked for some sort of transliteration module or such, but they're all preset for Latin<->Cyrillic and aren't customizable in the least.

Second, I found some selection.replace macros for Excel, which do what I need except for one thing, I'm dealing with unicode phonetic symbols.

For example:

code:

    Selection.Replace What:=ChrW(&H19B), Replacement:=ChrW(&H29F), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

This works well, until I need to use a sequence of characters as input and/or output. The problem is I have no idea how to get Excel to accept a sequence of two ChrW(&Hxxx).

What I need this code to do, it take something like "tɬ" as input, and output "ʟ̣"

I could normally just use the Find/Replace window but I need to do this with 100 separate worksheets.

Alternatively if there was some sort of easier way to accomplish this, that would be great too!

# ¿ Nov 3, 2010 19:42

Adbot: ADBOT LOVES YOU

# ¿ May 5, 2024 22:11

Nebel: Sep 30, 2002; Soiled Meat

gwar3k1 posted:

Does simple concatenation work: "ChrW(&Hxxx) & ChrW(&Hxxx)"

code:

    sFind = ChrW(&H19B) & ChrW(&H20B)
    sReplace = ChrW(&H29F) & "t"
    Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

If it does, setup a sheet with two columns: Lang A, Lang B and enter your translations as needed, then use the following macro (or a modified version) to translate your sheets:

code:

Sub TranslateIt
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "TransLookUp" Then
      Sheets(i).Select
      iRow = 1
      Do While Sheets("TransLookup").Cells(iRow, 1) <> ""
        sFind = Sheets("TransLookup").Cells(iRow, 1)
        sReplace = Sheets("TransLookup").Cells(iRow, 2)
        Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _ 
             SearchOrder:=xlByRows, MatchCase:=True
        iRow = iRow + 1
      Loop      
    End if
  Next
End Sub

Excellent.

code:

   sFind = ChrW(&HF111)
    sReplace = ChrW(&H29F) & ChrW(&H323)
    Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

Worked fantastic.

Except I don't quite understand what your second macro does (I'm a Linguist and all I have is one year of computer science under my belt), all of my data is formatted so that column C contains the empty fields that I need to put the translation into, and column E contains the source.

# ¿ Nov 3, 2010 20:23

Nebel: Sep 30, 2002; Soiled Meat

gwar3k1 posted:

Sorry, the macro assumes you have a sheet named "TransLookup". On that sheet you have a column (A) which contains your original characters, and a second column (B) which contains the characters that col A should be changed to. I'm assuming your task is as simple as "change xx to yy" without any conditions (i.e. not accounting for "change xx to yy but if xx preceeds xxz then change it to ccc").

The macro loops all the worksheets in your book that aren't TransLookup (which it is using as reference) then it is making the replacement for every replacement you have instructed it to do (all rows on TransLookup).

Does that make sense?

Yeah that does, thank you very much. And it greatly simplifies matters!

# ¿ Nov 3, 2010 23:04

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread