Replace/substitute after this formula

[bweil]

Hello all,

I have a column with a lot of data combined with subtotals. I changed the format of the subtotals with the formula below:


=IF(ISNUMBER(SEARCH("Total";J1));RIGHT(J1;LEN(J1)-FIND(" ";J1;1))&" "&LEFT(J1;FIND(" ";J1;1)-1);J1)


So it the cell J1 contains: 123456 Total then cell K1(where I paste the formula) contains: Total 123456

So far so good. But we have to translate the word total to different languages. So here we can use ctrl+h and replace total by e.g. Gesamtsumme. But is it possible to build this in the formula? Like a double function? If the cell contains the word total then first translate to Gesamtsumme followed by the formula above.

Would really appreciate a reaction! I understand that an extra column with replace/substitute is possible, but ctrl+h is also very fast. So only a solution that beat ctrl+h

[Pete_UK]

Will you definitely have Total to begin with (i.e. in J1), or might that also be in a different language?

How many languages do you wish to cater for?

Is there some cell somewhere that you can use to specify the destination language that you want to use, and indeed the source language that you are trying to convert from (in another cell)?

It strikes me that you can have a lookup table listing the languages (or an abbreviation) and then the appropriate words in each language in an adjacent column, then in the SEARCH function you can have a VLOOKUP function for the source word and you can have another VLOOKUP function to replace the LEFT term.

Hope this helps.

Pete

Pete