Find names and replace with translations

[MrWouters]

Hi,

I previously had some help from Istvan extracting the %'s of fabrics from certain cells. Now I would need to translate the different fabric types, just the names not the %'s of course.

So I have the English names as starting point. Now I need to translate them to Dutch and French (more in the future, but lets start with this)
but again, I only need to replace the fabric name so the x-amount of % needs to stay in front. I've taken a random example below.

Column AN Column AO Column AP

Material Description EN

Material Description FR

Material Description NL

<tbody>
</tbody>

40% Acrylic, 33% Polyester, 27% Wool

<tbody>
</tbody>

etc.........

In another file, I have the English names in Column A, French in Column B, Dutch in Column C.

A	B	C
Eng	Fr	NL
Acrylic	Acrylique	Acryl
Polyester	Polyester	Polyester
Wool	Laine	Wol

<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>
</tbody>

Is there a formula I can use for this? I'm guessing any space between a two (or more) component fabric name will present a problem? So I would have to connect it with a - or something again?

Many thanks in advance once again for any support!

Regards,
Wouter

 

[FormR]

Try this version instead:

Function translate(s As String, tbl As Range, col As Long) As String
Dim i As Long
translate = Replace(s, ",", " |")
For i = 1 To tbl.Rows.Count
    translate = Trim(Replace(" " & translate & " ", " " & _
    tbl.Cells(i, 1) & " ", " " & tbl.Cells(i, col) & " ", , , vbTextCompare))
Next i
translate = Replace(translate, " |", ",")
End Function

 

[MrWouters]

Briljant! What is changed? I see it's a bit longer and I see a lot of "quotes"
Does this remove or ignore any possible spaces between words in the translation table or something? Because I did already try TRIM to remove all that.

By the way; this code would work with any random table of translations right? Say for instance I have color names in different languages. I do the same steps right?
Nothing in the code would need to change?

Well, either way, huge thanks! Genious stuff, probably childsplay for you guys, but I love it!

Have a great evening!

 

[FormR]

What is changed?

The "hair" in Mohair was being replaced by "Poil/crin" so instead of looking for "hair" in the string we are now looking for {space}hair{space}.

By the way; this code would work with any random table of translations right? Say for instance I have color names in different languages. I do the same steps right?
Nothing in the code would need to change?

It's not as generalised as I would have liked, but it will work for words separated by spaces or commas. I'm still in the learning stages with this stuff and I'm sure it could be written in a better, more general way.