Excel: Replace strings with a Matrix formula

[peterfarge]

Hello NG,

the following sheet:
- In column A are words in German.
- In column B are the translated words in English.
- In C1 is one English clause with only one single German word in it.

ToDo:
I want translate this word in English and write it o D1. But I dont
want to use VBA or more then one cell for the calculation. Such
solutions I can make for myself. I'm working since several years with
Excel and VBA.

Currently I'm playing with this matrix formula. (I have translated the
Excel function names from German to English, but I dont know if I used
the right words.)
{=IF(ISERROR(SEARCH(A1:A99;C1));"";B1:B99)}
If you mark the D column and insert this formula, only the translated
word will show up in D column. (Dont forget to remove the brackets and
press Ctrl+Shift+Enter to acknowledge the formula)

The next step could be the function Concatenate().
{=Concatenate(IF(ISERROR(SEARCH(A1:A99;C1));"";B1:B99))}
If I put this formula in D1, the estimated result should be that the
translated word show up in D1. But it does not work, because
Sum("A1:A99") works, Concatenate("A1:A99") will only return the
content of A1.


Can someone give me a hint what I can do?

Peter

 

[Billy Liddel]

Peter

How about SUBSTITUTE?

=IF(COUNTA(A2:B2)<>2,C2,SUBSTITUTE(C2,A2,B2))

Regards
Peter Atherton

 

[wknehans]

Peter,

It looks like your problem is in the Concatenate function, which is used to
combine multiple bits of text into one string. The format is:

{=Concatenate(Data1,Data2,Data3,...,)}

where Data n is a text string in quotes or a single cell reference
containing the data to be converted into a string. You can't specify
multiple cells or a range as one of the Data entries; each cell in the range
must be called separately, which can be a tedious process if you have lots of
cells to combine.

In your case, it looks like you want to use Concatenate to combine the
results of the IF statement, which would either lead to this:

{=Concatenate("")} if the ISERROR check returns TRUE

or this:

{=Concatenate(B1:B99)} if the ISERROR check is FALSE

Neither of those is a proper use of the Concatenate function. The first is
trying to combine a single element without specifying any other elements, and
in the second the syntax is incorrect. In the second case even if you used
the crrect syntax, you'd end up with :

{=Concatenate(B1,B2, B3,....,B98,B99)}

which would merely result in a bunch of English words being stuck together
as one long word. I don't think that's what you are looking for.

Perhaps you could provide more information on exactly what you want to
concatenate and what the result should look like.

Good luck,
BD

 

[peterfarge]

@Billy: As far as I can see, your formula does the same as my formula:
{=IF(ISERROR(SEARCH(A1:A99;C1));"";B1:B99)}
But I would still have the Concatenate problem.

@wknehans:
I have created an example:
http://www.free-space.at/blossom/Mappe1.zip

 

[Rick Rothstein \(MVP - VB\)]

Does this normally entered formula in D1 (for the worksheet you posted) do
what you want?

=INDEX(B1:B3,SUMPRODUCT(ISNUMBER(SEARCH(A1:A3,C1))*ROW(A1:A3)))

Rick

 

[peterfarge]

@Rick: Thanks a lot

 

[wknehans]

Peter,

You may not need to use the Concatenate function at all. If the formula you
are playing with returns a single word answer, you don't have anything to
concatenate.

But if you're looking beyond merely identifying and translating one word,
and you want to substitute that translated word back into the original
sentence, you will need Concatenate.

In that case it would look something like:

=Concatenate("Sentence up to German word ",formula for translating German_
word," Remainder of sentence")

Of course, if you are going to have varying sentences, you'll have to use
some other string manipulation functions (like MID, LEFT, RIGHT, LEN, etc.)
to identify "Sentence up to German word" and "Remainder of sentence" for each
sentence.

Hope this helps,
BD