SUBSTITUTE function overload - more than 64 levels of nesting

[trucyeetung]

Hi all, I created a formular to translate a sentence with many Vietnamese words inside one cell at loaciton: L3,
This SUBSTITUTE formular can be use to copy and paste to other cell for translaiton the next cells.
but this SUBSTITUTE fuction has limited in 64 levels of nesting (?). it mean that allows me translate 64 words only (?)

Could you pls help me or tell me how to created more than 64 SUBSTITUTE fuctions in one cell.

the detail of formular asfwl:
=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(L3),"#&VN",""),"BẠCH ĐÀN","eucalyptus"),"BỘT GIẤY","pulp"),"CÂY BẠCH ĐÀN","eucalyptus"),"CÂY KEO NGUYÊN LIỆU GIẤY","acacia for pulp"),"CỦA","of"),"DĂM GỖ","woodchips"),"DĂM GỖ CÂY KEO","acacia woodchips"),"DĂM GỖ CÂY TRÀM","acacia woodchips"),"DĂM GỖ KEO","acacia woodchips"),"DĂM GỖ KEO LÁ TRÀM","acacia woodchips"),"DĂM GỖ KEO TRÀM","acacia woodchips"),"DĂM GỖ RỪNG TRỒNG","woodchips of plantations"),"DĂM GỖ RỪNG TỰ TRỒNG","woodchips of plantations"),"DĂM GỖ TRÀM","acacia woodchips"),"DĂM GỖ TRÀM KEO","acacia woodchips"),"DĂM GỖ TỪ RỪNG TRỒNG","woodchips of plantations"),"DĂM MẢNH","woodchips"),"DĂM MẢNH GỖ KEO","acacia woodchips"),"ĐỂ","for"),"ĐỂ SẢN XUẤT BỘT GIẤY","for pulp"),"ĐI TÀU RỜI","bulk shipment"),"ĐỘ ẨM","moisture"),"ĐỘ ẨM TỰ NHIÊN","moisture"),"ĐỘ THỦY PHẦN","moisture"),"KEO","acacia"),"DÙNG","for"),"DÙNG ĐỂ","for"),"DÙNG LÀM","for"),"DUNG SAI","tolerance"),"GIÁ TRỊ KHAI BÁO","deleration value"),"GỖ CÓ KIỂM SOÁT","control wood"),"GỖ KEO","acacia"),"GỖ DĂM CÂY KEO","acacia woodchips"),"GỖ DĂM MẢNH","woodchips"),"GỖ KEO","acacia"),"GỖ RỪNG TRỒNG","plantations"),"HÀNG DĂM GỖ","woodchips"),"DĂM","woodchips"),"HÀNG RỜI","bulk cargo"),"HÀNG RỜI CÓ XUẤT XỨ TỪ RỪNG TRỒNG TRONG NƯỚC","new products of plantations"),"TRONG NƯỚC",""),"KHAI THÁC","harvest"),"KHỐI LƯỢNG KHAI BÁO","deleration value"),"KÍCH CỠ","size"),"KÍCH THƯỚC","size"),"KHÔ TUYỆT ĐỐI","bone dried"),"GỖ TRÀM","hardwood"),"TƯƠNG ĐƯƠNG","about"),"NGUỒN GỐC","source"),"NGUỒN GỐC TRỒNG","of plantations"),"NGUYÊN LIỆU","raw material"),"NGUYÊN LIỆU BỘT GIẤY","raw material for pulp"),"NGUYÊN LIỆU GIẤY","raw material for pulp"),"Ở","in"),"QUY KHÔ","dried"),"RỪNG TRỒNG","plantations"),"RỪNG TRỒNG TRONG NƯỚC","plantations"),"SẢN XUẤT","produce"),"SẢN XUẤT BỘT GIẤY","produce pulp"),"TẤN TƯƠI","MT"),"SỐ LƯỢNG","quantity"),"TẤN KHÔ","bdt"),"KEO TRÀM","acacia"))

Thank you

 

[Kamolga]

Hi,

I would personally first try some add-ins such as this https://support.office.com/en-us/ar...anslator-f262d0c0-991c-485b-89b6-32cc8d326889.
Otherwise when I do multilingual templates, I set up a 3 columns table: a column English, French and dutch. For each title, If English is selected, it vlookup column 1, 2 for French and 3 for Dutch (so all my titles are look up formula with 1 if).

If I needed to translate sentences, I would have a table with both languages then a UDF Translate(Cell Range) "for each word in Cell Range, look value in Table column1 and show what is in column 2"

 

[trucyeetung]

Thank Kamolga, the Excle Fuctions Translator seems not correct of translation, that why I should create my own translations formular.
because one English words may has several meaning, but Translator fuction could not use excatly meaning (after my trail and found this issue).
Googletranslate in spreadsheet also same problem.
I'm thinking how to use a matrix fuction in excel or VBA... (?) but I could not do.

 

[trucyeetung]

I just post this table for your reference:

[TABLE="width: 471"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Vietnames[/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD]bạch đàn[/TD]
[TD]eucalyptus[/TD]
[/TR]
[TR]
[TD]bột giấy[/TD]
[TD]pulp[/TD]
[/TR]
[TR]
[TD]cây bạch đàn[/TD]
[TD]eucalyptus[/TD]
[/TR]
[TR]
[TD]cây keo nguyên liệu giấy[/TD]
[TD]acacia for pulp[/TD]
[/TR]
[TR]
[TD]của[/TD]
[TD]of[/TD]
[/TR]
[TR]
[TD]dăm gỗ[/TD]
[TD]woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ cây keo[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ cây tràm[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ keo[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ keo lá tràm[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ keo tràm[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ rừng trồng[/TD]
[TD]woodchips of plantations[/TD]
[/TR]
[TR]
[TD]dăm gỗ rừng tự trồng[/TD]
[TD]woodchips of plantations[/TD]
[/TR]
[TR]
[TD]dăm gỗ tràm[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ tràm keo[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]dăm gỗ từ rừng trồng[/TD]
[TD]woodchips of plantations[/TD]
[/TR]
[TR]
[TD]dăm mảnh[/TD]
[TD]woodchips[/TD]
[/TR]
[TR]
[TD]dăm mảnh gỗ keo[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]để[/TD]
[TD]for[/TD]
[/TR]
[TR]
[TD]để sản xuất bột giấy[/TD]
[TD]for pulp[/TD]
[/TR]
[TR]
[TD]đi tàu rời[/TD]
[TD]bulk shipment[/TD]
[/TR]
[TR]
[TD]độ ẩm[/TD]
[TD]moisture[/TD]
[/TR]
[TR]
[TD]độ ẩm tự nhiên[/TD]
[TD]moisture[/TD]
[/TR]
[TR]
[TD]độ thủy phần[/TD]
[TD]moisture[/TD]
[/TR]
[TR]
[TD]đổ xá[/TD]
[TD]bulk cargo[/TD]
[/TR]
[TR]
[TD]dùng[/TD]
[TD]for[/TD]
[/TR]
[TR]
[TD]dùng để[/TD]
[TD]for[/TD]
[/TR]
[TR]
[TD]dùng làm[/TD]
[TD]for[/TD]
[/TR]
[TR]
[TD]dung sai[/TD]
[TD]tolerance[/TD]
[/TR]
[TR]
[TD]giá trị khai báo[/TD]
[TD]deleration value[/TD]
[/TR]
[TR]
[TD]gỗ có kiểm soát[/TD]
[TD]control wood[/TD]
[/TR]
[TR]
[TD]gỗ có nguồng gốc rừng trồng[/TD]
[TD]of plantations[/TD]
[/TR]
[TR]
[TD]gỗ dăm cây keo[/TD]
[TD]acacia woodchips[/TD]
[/TR]
[TR]
[TD]gỗ dăm mảnh[/TD]
[TD]woodchips[/TD]
[/TR]
[TR]
[TD]gỗ keo[/TD]
[TD]acacia[/TD]
[/TR]
[TR]
[TD]gỗ rừng trồng[/TD]
[TD]plantations[/TD]
[/TR]
[TR]
[TD]hàng dăm gỗ[/TD]
[TD]woodchips[/TD]
[/TR]
[TR]
[TD]hàng mới[/TD]
[TD]new products[/TD]
[/TR]
[TR]
[TD]hàng rời[/TD]
[TD]bulk cargo[/TD]
[/TR]
[TR]
[TD]hàng rời có xuất xứ từ rừng trồng trong nước[/TD]
[TD]new products of plantations[/TD]
[/TR]
[TR]
[TD]Hội quản lý rừng trồng quốc tế[/TD]
[TD]Forest Stewardship Council[/TD]
[/TR]
[TR]
[TD]khai thác[/TD]
[TD]harvest[/TD]
[/TR]
[TR]
[TD]khối lượng khai báo[/TD]
[TD]deleration value[/TD]
[/TR]
[TR]
[TD]kích cỡ[/TD]
[TD]size[/TD]
[/TR]
[TR]
[TD]kích thước[/TD]
[TD]size[/TD]
[/TR]
[TR]
[TD]khô tuyệt đối[/TD]
[TD]bone dried[/TD]
[/TR]
[TR]
[TD]làm[/TD]
[TD]for[/TD]
[/TR]
[TR]
[TD]lô hàng để rời[/TD]
[TD]bulk cargo[/TD]
[/TR]
[TR]
[TD]lượng khai báo[/TD]
[TD]deleration value[/TD]
[/TR]
[TR]
[TD]nguồn gốc[/TD]
[TD]source[/TD]
[/TR]
[TR]
[TD]nguồn gốc trồng[/TD]
[TD]of plantations[/TD]
[/TR]
[TR]
[TD]nguyên liệu[/TD]
[TD]raw material[/TD]
[/TR]
[TR]
[TD]nguyên liệu bột giấy[/TD]
[TD]raw material for pulp[/TD]
[/TR]
[TR]
[TD]nguyên liệu giấy[/TD]
[TD]raw material for pulp[/TD]
[/TR]
[TR]
[TD]ở[/TD]
[TD]in[/TD]
[/TR]
[TR]
[TD]quy khô[/TD]
[TD]dried[/TD]
[/TR]
[TR]
[TD]rừng trồng[/TD]
[TD]plantations[/TD]
[/TR]
[TR]
[TD]rừng trồng trong nước[/TD]
[TD]plantations[/TD]
[/TR]
[TR]
[TD]sản xuất[/TD]
[TD]produce[/TD]
[/TR]
[TR]
[TD]sản xuất bột giấy[/TD]
[TD]produce pulp[/TD]
[/TR]
[TR]
[TD]sản xuất giấy[/TD]
[TD]produce pulp[/TD]
[/TR]
[TR]
[TD]số lượng[/TD]
[TD]quantity[/TD]
[/TR]
[TR]
[TD]tấn khô[/TD]
[TD]bdt[/TD]
[/TR]
[TR]
[TD]tấn tươi[/TD]
[TD]mt[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

Hi,

The formula looks in Table1 column called Vietnames, so ​

for this to work you need to format this list as table (select it, insert tab, click table and keep this "Vietnames" as title in first column, English in second). It can be on any page of the workbook., even hidden.
Then you need to open vba: right click the sheet name, click code, then insert menu and chose module
Now paste this

Function Translate(Rng As Range) As String
Dim cell As Range
Dim result As String: result = Rng.Value
 For Each cell In Range("[COLOR=#0000ff]Table1[Vietnames][/COLOR]")
     result = Replace(result, cell.Value, cell.Offset(0, 1).Value)
 Next cell
 Translate = result
End Function

If you have Vietnamese text in cell A1, type

=translate(A1)

in any cell to have it translated. The good news is if you add words to the table, it will extend automatically and be used in formula.

Note: it look from top, so if you want to translate a group of words, put it before the words themselves. e.g: you want to translate
Five fingers as hand, you need to have Five Fingers before Fingers in the list. Otherwise it will first translate fingers and not recognise five fingers when it comes to it. Hope it is clear.

 

[trucyeetung]

Thank Kamolga, your guiding is perfect, I can do it and translate follow my table.

 

[trucyeetung]

Dear Kamolga,

I want to UPPER the cell A1 first, then translate by code:
=translate(upper(A1))
but it fail, and the result is:
#VALUE !

The reason I need to put UPPER formular that because there are many people input Vietnamese in cells A1, A2, A3..., sometime UP words, some time LOW words,
each memeber can free typing...

so I need to uniform the words become upper, then translate.

Thank you.

 

[Kamolga]

Hi,

You can upper(translate(A1)) for the result but not the other way around: the function is on a range, and you can not upper range (=area) but a string (=text).
One way to it would be

[COLOR=#333333][FONT=monospace]Function Translate(Rng As Range) As String
Dim cell As Range
Dim result As String: result = [/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=monospace]U[/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]case[/FONT][/COLOR][/FONT][/COLOR][COLOR=#333333][FONT=monospace](Rng.Value)
[/FONT][/COLOR]

Or change the first line

[COLOR=#333333][FONT=monospace]Function Translate(Rng As [/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana][COLOR=#ff0000][FONT=monospace]string[/FONT][/COLOR][/FONT][/COLOR][COLOR=#333333][FONT=monospace]) As String[/FONT][/COLOR]

but the function would only work with translate(Upper()) or typed text for example.

My preferred option would be that everytime someone types something in a1:a10 (vietnames) for example, it will automatically get UPPER, and when they type in B1:B10 (English) it will get Proper case.

To do that, right click the sheet name, click 'code' and paste

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
'Upper cells (vietnamese)
Dim UpperCells As Range: Set UpperCells = Range("[/FONT][/COLOR][COLOR=#0000ff][FONT=Verdana]A1:A10[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]")
If Not Application.Intersect(UpperCells, Range(Target.Address)) Is Nothing Then
    Target.Value = UCase(Target.Value)
End If
'Proper cells
Dim ProperCells As Range: Set ProperCells = Range("[/FONT][/COLOR][COLOR=#0000cd][FONT=Verdana]B1:B10[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]")
If Not Application.Intersect(ProperCells, Range(Target.Address)) Is Nothing Then
    Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub

there (not in a module like the translate function).
​

 

[Kamolga]

Code was split, it is in one piece of course

[LEFT][COLOR=#222222][FONT=monospace]Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
[/FONT][/COLOR][COLOR=#008000][FONT=monospace]'Upper cells (vietnamese)[/FONT][/COLOR][COLOR=#222222][FONT=monospace]
Dim UpperCells As Range: Set UpperCells = Range("[FONT=Verdana][COLOR=#0000ff]A1:A10[/COLOR]"[/FONT][FONT=monospace][FONT=Verdana])
[/FONT][/FONT][LEFT][FONT=monospace][FONT=Verdana]If Not Application.Intersect(UpperCells, Range(Target.Address)) Is Nothing Then
    Target.Value = UCase(Target.Value)
End If
[COLOR=#008000]'Proper cells[/COLOR]
Dim ProperCells As Range: Set ProperCells = Range("[/FONT][/FONT][COLOR=#0000cd][COLOR=#0000cd][FONT=monospace][FONT=Verdana]B1:B10[/FONT][/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#222222][FONT=monospace][FONT=Verdana]")
[/FONT][/FONT][/COLOR][/COLOR][/LEFT]
[COLOR=#222222][LEFT][COLOR=#222222][FONT=monospace][FONT=Verdana]If Not Application.Intersect(ProperCells, Range(Target.Address)) Is Nothing Then
    Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub[/FONT][/FONT][/COLOR][/LEFT]
[/COLOR][/FONT][/COLOR][/LEFT]
<strike></strike>

 

[trucyeetung]

Hi Kamolga, thank you for your help,
finaly, I choose your first option of your guiding.
it looks workable for my home work, I just type =translate(A1) then it can work with result as =translate(upper(A1). Thank you for your supporting in forum.
Function Translate(Rng As Range) As String
Dim cell As Range
Dim result As String: result = UCase(Rng.Value)
For Each cell In Range("Table1[Vietnames]")
result = Replace(result, cell.Value, cell.Offset(0, 1).Value)
Next cell
Translate = result
End Function