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

 

[WarPigl3t]

Would you be interested in a custom function?

 

[MrWouters]

Hi,
Sorry, really no expert so not sure if I would be able to execute it?? But open to new learnings, sure..

 

[WarPigl3t]

That's alright. I concluded that what you are asking is not impossible, but would require over an hour of my time. I'm too tired for that. Good night. Sorry.

 

[FormR]

Hi, you could give this UDF a try:

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

To install the code;
1. In Excel press ALT+F11 to open the VBE
2. Click Insert > Module
3. Paste the code above to the window on the right
4. Save the file as a macro enabled workbook

You can then use the UDF as per the example below:

Excel Workbook
	A	B	C	D	E	F	G
1	Eng	Fr	NL		Material Description EN	Material Description FR	Material Description NL
2	Acrylic	Acrylique	Acryl		40% Acrylic, 33% Polyester, 27% Wool	40% Acrylique, 33% Polyester, 27% Laine	40% Acryl, 33% Polyester, 27% Wol
3	Polyester	Polyester	Polyester
4	Wool	Laine	Wol
Sheet1

 

[MrWouters]

Hi,

Wow ok, I'm gonna give it a shot! And I can pul this formula down the whole column then?
Can the fabric names in different languages be in another file as well? or at least in another tab? I now have them in another file.

And say I would add another language in Column D. Very sorry but I just got word German might be needed too. So Column D would be German then.
Woud I just expand the range in the formula? Or does the code need to be adjusted as well?

I'll go ahead and give this a go already anyway's. Many thanks!

 

[FormR]

And I can pul this formula down the whole column then?

Yes.

Can the fabric names in different languages be in another file as well? or at least in another tab? I now have them in another file.

Different tab yes, different file - only if that file is open.

And say I would add another language in Column D. Very sorry but I just got word German might be needed too. So Column D would be German then.
Woud I just expand the range in the formula? Or does the code need to be adjusted as well?

The code wouldn't need to change - just expand the table and expand the formula to use the whole table, the 3rd argument of the function says which column to use for the "translated" words.

This version is not case sensitive - the first one was:

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

I'll go ahead and give this a go already anyway's. Many thanks!

Good luck, let us know how you get on!

 

[MrWouters]

Wow this is really cool, it works! You knew that obviously But I am already no expert, and in the area of Macro's or UDF's absolutely clueless even. But very cool this!

Only thing I cant figure out is below translation. I must be overlooking something?

I get below for Mohair.

18% Wool, 72% Polyamide, 10% Mohair

18% Laine, 72% Polyamide, 10% MoPoil/crin

18% Wol, 72% Polyamide, 10% MoHaar

<tbody>
</tbody>

While the table is as below:

Wool	Laine	Wol
Mohair	Mohair	Mohair
Alpaca	Alpaga	Alpaca

<tbody>
</tbody>

Wool it grabs just fine. What am I missing?

 

[FormR]

Wool it grabs just fine. What am I missing?

Not sure, is that the full table of translations? Could you post the actual formula and the full translation table if it's not too excessively big?

Excel Workbook
	A	B	C	D	E
1	Wool	Laine	Wol		18% Wool, 72% Polyamide, 10% Mohair
2	Mohair	Mohair	Mohair		18% Laine, 72% Polyamide, 10% Mohair
3	Alpaca	Alpaga	Alpaca
Sheet2

 

[MrWouters]

Cell	Formula
AO317	=translate($AN317,'[Materials trans.xlsx]Fabric trans'!$A$2:$C$73,2)

<tbody>
</tbody>

This is the full translation table, as I said German was needed too so I added that one for now.

	A	B	C	D
1	Eng	Fr	NL	De
2	Abaca	Abaca	Abaca	Manilla
3	Acetate	Acétate	Acetaat	Azetat
4	Hemp	Chanvre	Hennep	Hanf
5	Coir	Fibre de coco	Kokos	Kokos
6	Cotton	Coton	Katoen	Baumwolle
7	Cupro	Cupro	Cupro	Cupro
8	Lyocell	Lyocell	Lyocell	Lyocell
9	Down	Duvet	Dons	Daune
10	Elastane	Elasthanne	Elasthan	Elasthan
11	Elastodiene	Élastödiène	Elastodieen	Elastoiden
12	Feather	Plume	Veren	Feder
13	Flax	Lin	Vlas	Flachs
14	Hair	Poil/crin	Haar	Haar
15	Cotton linen	Toile metisse	Half linen	Halbleinen
16	Jute	Jute	Jute	Jute
17	Kapok	Kapok	Kapok	Kapok
18	Linen	Lin	Linnen	Leinen
19	Modacrylic	Madacrylique	Modacryl	Modacryl
20	Modal	Modal	Modal	Modal
21	Métal métallique	Metallisch	Metaal	Metall
22	Polyamide	Polyamide	Polyamide	Polyamide
23	Acrylic	Acrylique	Acryl	Acryl
24	Polyethylene	Polyéthylène	Polyethyleen	Polyäthylen
25	Paper	Papier	Papier	Papier
26	Polyester	Polyester	Polyester	Polyester
27	PVC	PVC	PVC	PVC
28	Polypropylene	Polypropylène	Polypropyleen	Polypropylen
29	Polyurethane	Polyuréthanne	Polyurethaan	Polyurethan
30	Ramie	Ramie	Ramie	Ramie
31	Silk	Soie	Zijde	Seide
32	Sisal	Sisal	Sisal	Sisal
33	Triacetate	Tri-acétate	Tri-acetaat	Triacetat
34	Fiber blend	Mélange de fibres	Vezel menging	Faser mischung
35	Viscose	Viscose	Viscose	Viskose
36	Viscose with PU coating	Viscose avec Enduction Polyurethanne	Viscose met polyurethaan deklaag	Viskose mit Polyurethan beschichtung
37	Polyester with PU coating	Polyester avec Enduction Polyurethanne	Polyester met polyurethaan deklaag	Polyester mit Polyurethan beschichtung
38	Vinyl	Vinylal	Vinyl	Vinylal
39	Angora	Angora	Angora	Angora
40	Beaver	Castor	Bever	Biber
41	Vicuna	Vicuna	Vicuna	Vikunja
42	Camel	Chamaeu	Kameel	Kamel
43	Lama	Vigogne	Lama	Lama
44	Wool	Laine	Wol	Wolle
45	Mohair	Mohair	Mohair	Mohair
46	Alpaca	Alpaga	Alpaca	Alpaca
47	Cashme re	Cachemi re	Kasjmier	Kaschmi r
48	Otter	Loutre	Otter	Otter
49	Guanaco	Guanaco	Guanaco	Guanako
50	Virgin wool	Pure laine viege	Zuiver Scheerwol	Reine Schurwolle
51	Lambs wool	Laine d’agneau	Lamswol	Lammwolle
52	Merino wool	Laine merino	Merino wol	Merinowolle
53	Shetland wool	Laine d’Shetland	Shetland wol	Shetlandwolle
54	Lambskin	Peau d’agneau	Lamsvacht	Lammfell
55	Other fibers	Autres fibres	Andere vezels	Sonstige Fasern
56	Pig suede	Cochon suede	Varkens suede	Schweins leder
57	Sheep	Mouton	Schapenleer	Schaf
58	Genuine leather	Cuir véritable	Echt leer	Echtes leder
59	Pig leather	Pécari	Varkensleer	Schweinleder
60	Cow leather	Cuir de vache	Koeien leer	Kuhleder
61	Yak	Yack	Yak	Yak
62	Cow suede	Suède de vache	Koeien suede	Kuhveloursleder
63	Cow	Vache	Koe	Kuh
64	Buffalo leather	Cuir de buffle	Buffelleder	Büffelleder
65	Copper	Cuivre	Koper	Kupfer
66	Goat leather	Cuir de chèvre	Geiten leer	Zeigefell
67	Goat suede	Chèvre velours	Geiten suede	Ziegen velour
68	Fake Fur	Fourrure Fausse	Nep Bont	Nepp Pelz
69	Rabbit fur	Fourrure de lapin	Konijnenbont	Karnickelpelz
70	Tin	Étain	Tin	Zinn
71	Neoprene	Néoprène	Neoprene	Neoprene
72	Straw	Paille	Stro	Stroh
73	Steel	Acier	Staal	Stahl

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 163px"><col style="WIDTH: 248px"><col style="WIDTH: 223px"><col style="WIDTH: 252px"></colgroup><tbody>
</tbody>