Help needed with automatically substituting texts

Hi all,


we are translating our website (more than 5000 products) into German. Doing all the translations by hand would be an enormous amount of work, that is why we thought of using "building blocks", where an English phrase would be substituted by its German equivalent.
So if you have the sentence
"This product has a height of 30 cm and a width of 20 cm",
"This product has a height of" would be one building block, and "and a width of" would be another.


I am now searching for an Excel formula that automatically substitutes these building blocks with their German equivalents. So what I need is a formula that does the following:


"If any cell in column A contains the same text as cell B1, this text in column A should be substituted by the text as in cell C1."


But, of course, the cells in A do not match the text of B1 100% as they consist of more building blocks than 1.
I have been looking at the IF( function, but I am not sure if this will work.


Could anyone help me out? And please also tell me if it is not possible at all.


Thanks!

So in the case that A1 has multiple blocks, then what would column B contain?

Also what is the maximum number of potential blocks in a single cell?

Hi NBVC,

column B would, ideally, contain a list of all the building blocks, so that they are substituted one by one. If this would be impossible, it would contain only one block per time, which is replaced every time with a new building block, so that it is substituted step by step.

What I am envisioning (if you want to use formulas) is....

First make a list somewhere else in the sheet of all the "English" blocks possible, and next to those the "German" equivalents. (Say this list is in R2:S100 - with no blank cells).

Then say your original "English List" is in column A

In Column B put formula like:

=IFERROR(SUBSTITUTE(A1,LOOKUP(9.99999999E+307,SEARCH($R$2:$R$100,A1),$R$2:$R$100),LOOKUP(9.99999999E+307,SEARCH($R$2:$R$100,A1),$S$2:$S$100)),"")

Copy this formula across as many columns as you think would be equal to maximum number of potential blocks in a cell (say 5)

Then in the next available column enter formula:

=LOOKUP(2,1/(B1:F1<>""),B1:F1)

where B1:F1 contains the previous formulas

Now copy all those formulas down the list.

That last column will contain the final translated text.

Copy that column and Paste Special >> Values over itself to loose the formulas. Now you can delete all the columns between, so that Column B is now Column A translated.

Will that work for you?

Hi,

not quite yet. I have submitted the formulas as you described, but it doesn't substitute the English with the German blocks.

Can you post a sample workbook?

Hi,

please check the attachment with two examples.

I did make some changes:
- I replaced the English to Dutch equivalents (but I guess you'll see them in English anyway?)
- The 9.99999999E+307 wasn't working for me, so I inserted 100000000000000 .
- I replaced the , with ; as the , didn't work.

It seems, now, that some of the texts are replaced, while others aren't.

Yeah, looks like my method might not work. You have too many single word replacements and subsets of larger blocks, etc...

Not sure of the best way to handle it otherwise...

Ok, I just did a search and found a VBA User-Defined Function that can possibly work (http://www.get-digital-help.com/2013/02/27/substitute-multiple-text-strings-vba/).

Hit ALT+F11 and then Insert|Module and paste this VBA code in the editor:


Then in B2 of the worksheet, use formula:

=SubstituteMultiple(A2,$R$2:$R$269,$S$2:$S$269)

Edit: The only problem I might foresee is that it looks like the code as soon as it finds a replaceable word or phrase (starting from top), then it will replace... so if a better suited replacement occurs further down your list, it probably won't be used leaving you with possibly some residual English words.

Wow. That looks great and seems to work a lot better. The capitals seem to be missing, but we could change that manually I guess.
I have already thought about this "Better suited replacement", but what I will do is sort the building blocks on length, so that the longest blocks are replaced first.

Thank you so much!

Never mind, I already found the version that is case sensitive!