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:

Vietnames	English
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
đổ xá	bulk cargo
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ỗ có nguồng gốc rừng trồng	of plantations
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
hàng mới	new products
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
Hội quản lý rừng trồng quốc tế	Forest Stewardship Council
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
làm	for
lô hàng để rời	bulk cargo
lượng khai báo	deleration value
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
sản xuất giấy	produce pulp
số lượng	quantity
tấn khô	bdt
tấn tươi	mt

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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