Find & replace Japanese to English

[my8950]

Trying to create a tool to convert Japanese characters to English.

I use a software tool that generates a CSV file, problem for me is, most of the column names are Japanese.
What I have done so far is copy the Japanese and input to column B in spreadsheet, and then put google translate into column C.
I'm on my 150th term and I still have many to go.
I figured I should start working on this to see if it is logical to even try.
Basically I want to make a tool that will look in the output file and find any of the terms in Column B, and convert them to column C.

Does this sound do-able? I think so, but I'm looking for some suggestions and ideas.

Thank you!

 

[mrmmickle1]

Is it possible to use a simple vlookup? I would just set up a mapping table and then add things as you go....

Note: Japanese Characters have been rendered as ???? but, I tested with real Japanese characters and the Vlookup() works no problem....

Excel 2010
	A	B	C	D	E
1	Japenese	English Translation		???	Some Stuff 3
2	????	Some Stuff 1		????	Some Stuff 1
3	????	Some Stuff 2
4	???	Some Stuff 3
Sheet1

Cell Formulas
Range	Formula
E1	=VLOOKUP(D1,$A$1:$B$4,2,FALSE)
E2	=VLOOKUP(D2,$A$1:$B$4,2,FALSE)

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]

 

[my8950]

Wouldn't I need to create that on a blank page and reference the output page from the software tool?
Maybe sheet1 I copy the output from the software tool
Sheet 2 has my A = B conversion
Sheet 3 would contain the VLOOKUP formulas referencing sheet 1?

 

[my8950]

For example:
( ファイナル比 ) equals ( Final Ratio )

I put parentheses and a space before and after, that is not in my original conversion list.
The japanese word is in B7 and what I want to replace it with is in C7.

I have B1 - B116 containing the Japanese and C 1 - C116 containing the English conversion.

I want to search the B* column and if found, replace with the corresponding C* column location.

basically like a Control F, find XXX and Replace All with YYY.

 

[mrmmickle1]

I think you can just use a vlookup based on your description but if you want something else here is some code to accomplish your goal too:

[COLOR=#0000ff]Sub [/COLOR]TransLate()


[COLOR=#008000]    'Declare Variables[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] LngLp [COLOR=#0000ff]As Long[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] LRow[COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]refSht [COLOR=#0000ff]As [/COLOR]Worksheet
[COLOR=#0000ff]    Dim [/COLOR]ReplcRng [COLOR=#0000ff]As[/COLOR] Range
    
[COLOR=#0000ff]    Set[/COLOR] refSht = Sheets("YourRefTable")
[COLOR=#0000ff]    Set[/COLOR] ReplcRng = Sheets("ReplaceMe").Range("A1:A10")[COLOR=#008000] 'Your Replace Range Here[/COLOR]
 
    LRow = refSht.Cells(Rows.Count, "B").End(xlUp).Row [COLOR=#008000]'Define Last Row based on coluimn B of refSht[/COLOR]
    
[COLOR=#0000ff]    For[/COLOR] LngLp = 1 [COLOR=#0000ff]To [/COLOR]LRow
[COLOR=#008000]        'Find and Replace Data[/COLOR]
        ReplcRng.Replace What:=refSht.Cells(LngLp, "A"), Replacement:=refSht.Cells(LngLp, "B"), LookAt:=xlPart
[COLOR=#0000ff]    Next [/COLOR]LngLp
    
[COLOR=#0000ff]End Sub[/COLOR]

BEFORE:

Excel 2010
	A	B
1	1	One
2	2	Two
3	3	Three
4	4	Four
5	5	Five
YourRefTable

Excel 2010
	A
1	1
2	1
3	1
4	2
5	2
6	2
7	3
8	3
9	3
10	4
ReplaceMe

AFTER:

Excel 2010
	A
1	One
2	One
3	One
4	Two
5	Two
6	Two
7	Three
8	Three
9	Three
10	Four
ReplaceMe

 

[my8950]

Thank you for your time and help.
I'll give this a try and see if I can adapt it to what I'm doing.

 

[mrmmickle1]

You're welcome. Feel free to post back if you need additional assistance.

 

[my8950]

Seems to have trouble here:
Set refSht = Sheets("YourRefTable")

The Japanese that I want to convert, or my reference table is here:
=find!B2:B117
The corresponding English term is here:
=find!C2:C117

Sheet with my conversions is called find.

I tried a few different ways to enter, but no luck.
The code gets stuck in these lines.
- Set refSht = Sheets("=find!B2:B117")
- Set ReplcRng = Sheets("ReplaceMe").Range("=find!C2:C117") 'Your Replace Range Here

As you can see/tell, I'm "beginner" level here, if that...

 

[mrmmickle1]

I've added additional notations in order to help. Please see text in red and green

Sub TransLate()


    'Declare Variables
    Dim LngLp As Long
    Dim LRow As Long
    Dim refSht As Worksheet
    Dim ReplcRng As Range
    
    Set refSht = Sheets("[B][COLOR=#ff0000]Find[/COLOR][/B]")[COLOR=#008000] 'Define sheet with find items[/COLOR]
    Set ReplcRng = Sheets("[B][COLOR=#ff0000]ReplaceMe[/COLOR][/B]").Range("[B][COLOR=#ff0000]A1:A10[/COLOR][/B]") [COLOR=#008000]'Your Replace Range Here Change Sheet Name and Range[/COLOR]
 
    LRow = refSht.Cells(Rows.Count, "B").End(xlUp).Row[COLOR=#008000] 'Define Last Row based on column B of Find Sheet looks like this will be 117 based on your data[/COLOR]
    
    For LngLp = 1 To LRow
[COLOR=#008000]        'Cycle through your Replace Range ....this essentially cycles through all 116 terms and finds and replaces them in your range[/COLOR]
        ReplcRng.Replace What:=refSht.Cells(LngLp, "A"), Replacement:=refSht.Cells(LngLp, "B"), LookAt:=xlPart
    Next LngLp
    
End Sub

 

[my8950]

Ok, below is what I have so far.

Sub TransLate()


'Declare Variables
Dim LngLp As Long
Dim LRow As Long
Dim refSht As Worksheet
Dim ReplcRng As Range

Set refSht = Sheets("find") 'Define sheet with find items
Set ReplcRng = Sheets("find").Range("B2:B117") 'Your Replace Range Here Change Sheet Name and Range

LRow = refSht.Cells(Rows.Count, "B").End(xlUp).Row 'Define Last Row based on column B of Find Sheet looks like this will be 117 based on your data

For LngLp = 1 To LRow
'Cycle through your Replace Range ....this essentially cycles through all 116 terms and finds and replaces them in your range
ReplcRng.Replace What:=refSht.Cells(LngLp, "B"), Replacement:=refSht.Cells(LngLp, "B"), LookAt:=xlPart
Next LngLp

End Sub

If I replace this B with 117, or B117 it gets angry, runtime error 1004.
LRow = refSht.Cells(Rows.Count, "B").End(xlUp).Row 'Define Last Row based on column B of Find Sheet looks like this will be 117 based on your data

If I leave it as B or C, it will run through without error, but it is also not changing anything.


I had a question also about the find sheet with the Japanese, there is not reference to range for the find terms. Does that matter?
I'd think that in order to get the corresponding English term it would need to match.
Say I have ファイナル比 in sheetname find, Cell B8.
The english translation is in sheetname find, Cell C8.
How would this know to replace one with the other?