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.

 

[Yogi Anand]

Hi ak_swedegirl:

With Jindon, you are in great hands. However, just for the fun of it, I wanted to illustrate that in theory your idea of using a sort of VLOOKUP is feasible ...

Book1
	A	B	C	D	E	F	G	H	I	J
1	%	a
2	*	b
3	^	c	5	%&$*@	a	g	e	a	d
4	@	d
5	$	e
6	!	f
7	&	g
8
Sheet1

array formula for cells E3:I3 is ...

=VLOOKUP(MID(D3,COLUMN(INDIRECT("1:"&C3)),1),$A$1:$B$7,2,0)

How practical it would be to use I can't say. And if you do want to this sort of formula, further work will need to be done to do some fine tuning. Good Luck with your project, and keep us posted with your progress!

 

[jindon]

Yogi,

I'd never thought the problem is like you described...

I totally misunderstood the problem...

thanks for that

function manyo(rng as range, txt as string) as string
  for i=1 to len(txt)
    set r=rng.columns(1).find(mid$(txt,i,1))
    if not r is nothing then manyo=manyo & r.offset(,1).value
  next
end function

 

[ak_swedegirl]

Thank you both!

Once again, I'm at home where Mac Excel doesn't want to cooperate, so I'll test tomorrow in my office. (It cooperates with VLOOKUP, actually, but I left the file at work this time. D'oh!)

 

[Yogi Anand]

Hi ak_swedegirl:

You would have noticed that the * character did not correctly lend itself to be VLOOKed up. So I had to coerce the found * to be ~* to enable it be correctly VLOOKed up. So, I have modified the array formula for cells E4:I4 as ...

=VLOOKUP(IF(MID(D4,COLUMN(INDIRECT("1:"&C4)),1)="*","~*",MID(D4,COLUMN(INDIRECT("1:"&C4)),1)),$A$1:$B$7,2,0)

as illustrated in ...

Book1
	A	B	C	D	E	F	G	H	I	J
1	%	a
2	*	b
3	^	c	5	%&$*@	a	g	e	a	d
4	@	d	5	%&$*@	a	g	e	b	d
5	$	e
6	!	f
7	&	g
8
Sheet1

Once we have the basis clearly understood, perhaps a combination approach using the formulas from the native spreadsheet and the automation of VBA may be the way to go.

 

[ak_swedegirl]

=VLOOKUP(IF(MID(D4,COLUMN(INDIRECT("1:"&C4)),1)="*","~*",MID(D4,COLUMN(INDIRECT("1:"&C4)),1)),$A$1:$B$7,2,0)

That works like a charm! Thanks!

Now I just have to work through it so I understand what each part of it is doing, so the next time I won't need spoonfeeding. :wink:

In the long run, I'd like to make it so that the user can choose from a couple of transliteration schemes, but that shouldn't be too difficult (just change the lookup range).

Jindon, I'll test the revised VBA method tomorrow.

 

[Yogi Anand]

Hi ak_swedegirl:

I know I shouldn't be speculating, but I thought of the following two things ...

1) that your string may be of variable length

2) you may want to cocatenate the VLOOKed up values

So ...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	%	a
2	*	b
3	^	c	5	agebd	%&$*@	a	g	e	b	d
4	@	d
5	$	e
6	!	f
7	&	g
8
Sheet1 (2)

For this illustration I assumed that the maximum number of characters in the string might be 11 ... actually with my current setup you could have upto 251 characters going from column F to IV

so the array formula for cells F3:P3 is ...

=IF(COLUMN(INDIRECT("1:"&C3))>C3,"",VLOOKUP(IF(MID(E3,COLUMN(INDIRECT("1:"&C3)),1)="*","~*",MID(E3,COLUMN(INDIRECT("1:"&C3)),1)),$A$1:$B$7,2,0))

And I have the cocatenated string in cell D3

formula in cell D3 is ... =MCONCAT(F3:P3)

 

[ak_swedegirl]

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

OK, I tried that, and it just returns whatever was in b3 to start with (either that, or it's returning what was in the left column of sheet2, not the right column).

Working my way through the other responses...

 

[ak_swedegirl]

function manyo(rng as range, txt as string) as string
  for i=1 to len(txt)
    set r=rng.columns(1).find(mid$(txt,i,1))
    if not r is nothing then manyo=manyo & r.offset(,1).value
  next
end function

Jindon, this version works perfectly. Thanks for all the trouble!

 

[ak_swedegirl]

I know I shouldn't be speculating, but I thought of the following two things ...

1) that your string may be of variable length

2) you may want to cocatenate the VLOOKed up values

Yes, those are both true. Most strings will be 31 (because it's tanka poetry of 5-7-5-7-7 syllable structure), but some are quite a bit longer. The data will be most useful if concatenated, too.

For this illustration I assumed that the maximum number of characters in the string might be 11 ... actually with my current setup you could have upto 251 characters going from column F to IV

so the array formula for cells F3:P3 is ...

=IF(COLUMN(INDIRECT("1:"&C3))>C3,"",VLOOKUP(IF(MID(E3,COLUMN(INDIRECT("1:"&C3)),1)="*","~*",MID(E3,COLUMN(INDIRECT("1:"&C3)),1)),$A$1:$B$7,2,0))

And I have the cocatenated string in cell D3

formula in cell D3 is ... =MCONCAT(F3:P3)

The array formula does work, but =MCONCAT(F3:P3) does not. (I'm not familiar with MCONCAT - is it an add-in?). Also, if I use the array formula, I have to enter it into all possible columns that could contain a result? Not sure how to word that. What I mean is, if one poem has 12 items in the string, and another has 31, the array formula needs to be in 12 columns for the first item and 31 columns for the second, right? Do I just have to manually insert the array formula for each poem based on how long it is?

 

[Yogi Anand]

The array formula does work, but =MCONCAT(F3:P3) does not. (I'm not familiar with MCONCAT - is it an add-in?).

Yes it is from MoreFunc ... a free add-in

Also, if I use the array formula, I have to enter it into all possible columns that could contain a result? Not sure how to word that. What I mean is, if one poem has 12 items in the string, and another has 31, the array formula needs to be in 12 columns for the first item and 31 columns for the second, right? Do I just have to manually insert the array formula for each poem based on how long it is?

You can enter the formula once for the longest string and then copy it down for the remaining strings if they are of different lengths.

By the way, if VBA solution works for you, that may be the way to go!