Help with advanced find & replace (vlookup?)

[ak_swedegirl]

Hi all,

I'd like some advice on what to try with this. I don't mind tinkering with it myself, but I don't know the best way to go about it.

I'm working with Old Japanese, which I need to transliterate (i.e., put it into romanized script), because my analysis of the data requires looking at units smaller than one syllable. I don't want to do it manually because one text collection alone has 4500+ poems in it.

goal:
lookup each character in list, find its phonetic value, and return phonetic value (i.e., romanized version) of each piece of the text string in a new cell

what I have right now:
sheet 1:
ID# text results
4475 波都由伎波知敝尓布里之家 patuyukyipatipyenipurisikye


sheet 2: (this is not a complete list, just a sample)
character value
乳 ti
敝 pye
知 ti
布 pu
家 ke
乾 Npi
都 tu
事 si
二 ni
尓 ni
伎 kyi
里 ri
于 u
五 i
井 wi
人 ni
波 pa
由yu
之 si

What I need to do is VLOOKUP, sort of, but I need to replace every character in the text cell with its value from sheet 2.

example (I hope the Japanese shows up):
波都由伎波知敝尓布里之家

should return:
patuyukyipatipyenipurisikye

I know how to use VLOOKUP, at least for less sophicated tasks, but is there anyway to get it to look up each item without each one being in its own cell? Or is there a better way to tackle this beast than VLOOKUP? A simple find and replace seems horribly inefficient, particularly because there are over 900 possible characters that I'd have to run through (for EACH character in each string).

Any suggestions at all much appreciated! Thanks.

ETA: Forgot to mention that I'm using Excel 2003.

 

[jindon]

are those charactors imported from other application or just pasted as values?

If so, only the vba may be the solution, however, it will sometime
gives different results for particular charctors.

 

[ak_swedegirl]

are those charactors imported from other application or just pasted as values?

They're just text, copied and pasted from an online version of Man'yoshu. (I'm going to work on importing it in a more efficient manner, but first I want to make sure I can automate the transliteration.)

If so, only the vba may be the solution, however, it will sometime
gives different results for particular charctors.

VBA is fine, but what do you mean it might give different results for particular characters? If I have a list defining the value for each character, I want it to use the values assigned. I'm not sure I understand what you're saying.

 

[jindon]

You can only retrieve the"sound" with getphonetic method for these type of data.

And, as you know, each Japanese charactor has several ways of "sound",
whcih is called "ON YOMI" and "KUN YOMI" and each"YOMI" has several ways sometime.

getphonetic only retrieve "ON YOMI" class of phonetics.

therefore, If the creator meant to use "KUN YOMI", it will fail as a result of getphonetic method, but there will be no other way to do it.

 

[ak_swedegirl]

You can only retrieve the"sound" with getphonetic method for these type of data.

And, as you know, each Japanese charactor has several ways of "sound",
whcih is called "ON YOMI" and "KUN YOMI" and each"YOMI" has several ways sometime.

getphonetic only retrieve "ON YOMI" class of phonetics.

therefore, If the creator meant to use "KUN YOMI", it will fail as a result of getphonetic method, but there will be no other way to do it.

Oh, you're talking about modern Japanese. If I were using modern, then yes, your suggestion would be a quick way of getting the values.

The entire reason for using a table listing each character with its phonetic value is that:
1) manyogana (Old Japanese usage of Chinese characters) were on-yomi ONLY, so I don't need to worry about kun-yomi.
2) the on-yomi readings back then weren't necessarily the same as they are in modern Japanese - if I use the modern values, I'll get faulty data.
3) there were more vowels in Old Japanese, so if I use a replacement schematic based on modern Japanese, it won't give me accurate data that tells me which vowel it was in Old Japanese.

If it were just a matter of fetching the modern phonetic values, I could just paste it into Word and turn on "show rubi" and be done with it. The problem is that I don't want the modern Japanese reading, but the Old Japanese reading.

That's why I need a VLOOKUP or something else where the program can look up the Old Japanese value assigned to the character.

 

[ak_swedegirl]

REVISED EXAMPLE (so the fact that it's in Japanese won't be a stumbling block):

goal:
lookup each character in list, find its value, and return value of each piece of the text string in a new cell

what I have right now:
sheet 1:
ID# text results
4475 %&$*@ agebd


sheet 2: (this is not a complete list, just a sample)
character value
% a
* b
^ c
@ d
$ e
! f
& g

What I need to do is VLOOKUP, sort of, but I need to replace every character in the text cell with its value from sheet 2.

example:
%&$*@

should return:
agebd

In addition, if the character is not found in the table of values, I'd like it to return the character so I can flag it for manual transliteration.

Thanks!

 

[jindon]

Now
try this one
UDF
1) hit Alt + F11 to open vb editor
2) go to [Insert] -> [Module] then paste the code
3) click x to close the window to get back to excel
if raw data in A1:A100 and lookup value in B1 then

=manyo(a1:a100,b1)

Function manyo(rng As Range, txt As String) As String
Dim r As Range, x
For Each r In rng
    x = InStr(r.Value, txt)
        If x > 0 Then
            manyo = Right(r.Value, Len(r.Value) - (x + Len(txt)) + 1)
            Exit Function
        End If
Next
End Function

or

Function manyo(rng As Range, txt As String) As String
Dim r As Range, x
Set r = rng.Find(txt, , , xlPart)
If Not r Is Nothing Then
    manyo = Mid$(r.Value, InStr(r.Value, txt) + Len(txt))
Else
    manyo = txt
End If
End Function

use whichever faster one.....
please note: you cannot place both function at the same time, due to they are same function name...

 

[ak_swedegirl]

Thank you, jindon! I appreciate the help very much.

I just left the office, so I'll test it tomorrow. I tested it just now and it didn't work, but that's probably because I use Excel 2004 (Mac) at home.

Thanks!

 

[ak_swedegirl]

OK, I've had a chance to test both of these, and neither of them works for me. It's probably because I'm not sure which data you mean by "raw data", although I tested a number of variations. The second one doesn't return an error, but it doesn't return the phonetic values, either. It just gives an exact copy of the poem.

if raw data in A1:A100 and lookup value in B1 then

=manyo(a1:a100,b1)

If "raw data" is the poem, that's in b3. If "raw data" is the set of phonetic values, the kanji are in a1:a1900 (on another sheet) and the phonetic values are in b1:b1900. The set of kanji/phonetic value correspondences is a named range called manyogana.

To me, "lookup value" implies the phonetic value corresponding to any kanji; however, that doesn't make logical sense given the formula, because it's not contained in one cell. Then I assume it must be the poem, since that IS in one cell, but that doesn't work, either.

So I'm afraid I'm very confused. Would you mind spelling it out for a newbie like me?

I tried =manyo(manyogana, b3), =manyogana(a1:b1900, b3) and quite a few other variations, but nothing worked.

 

[jindon]

OK,
I thought the data in the same cell..
use in cell like
=manyo(sheet2!a1:b1900,b3)

Function manyo(rng As Range, txt As String) As String
Dim r As Range, x
Set r = rng.Columns(1).Find(txt, , , xlPart)
If Not r Is Nothing Then manyo = r.Offset(, 1).Value
End Function