Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Help needed with automatically substituting texts

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0

    Help needed with automatically substituting texts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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?


  3. #3
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    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.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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?


  5. #5
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Hi,

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

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook?


  7. #7
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    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.
    Attached Files Attached Files

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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...


  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Ok, I just did a search and found a VBA User-Defined Function that can possibly work (http://www.get-digital-help.com/2013...t-strings-vba/).

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

    Code:
    Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
    Dim i As Single
    For i = 1 To old_text.Cells.Count
        Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
        text = Result
    Next i
    SubstituteMultiple = Result
    End Function
    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.
    Last edited by NBVC; 2014-09-15 at 04:06 PM.


  10. #10
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    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!

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •