OpenOffice Calc - How to substitute multiple characters?

Question

I need to substitute non-English characters in French words to the closest English charachers. For example: "Combien ça coûte" Becomes "Combien ca coute"

I also need to remove some punctuation. Is there a way to do that without using nested SUBSTITUTE function or adding a dozen of extra columns?

Thanks.

Answer 1

This works for me:

=SUBSTITUTE(SUBSTITUTE(yourcell; CHAR(x1); CHAR(x2)); CHAR(y); CHAR(y1))

2 substitutions

=SUBSTITUTE(SUBSTITUTE((yourcell; CHAR(x1); CHAR(x2)); CHAR(y); CHAR(y1)); CHAR(z1); CHAR(z2))

3 substitutions

In your example, for replacing ç with c , and û with u

=SUBSTITUTE(SUBSTITUTE(yourcell; CHAR(231); CHAR(99)); CHAR(251); CHAR(117))

To find the code of any letter, simply use UNICODE function, for example:

=UNICODE("ç")

returns 231.

If you want to remove punctuation using substitute use this syntax (let's replace dots with nothing):

=SUBSTITUTE(yourcell; CHAR(46); "") 

Answer 2

It's also possible to use nested SUBSTITUTE() functions to preserve a character in some instances but change it in others:

In cell A1 I have:

Comedy, Music, Bonus Features

In cell B1 I want:

comedy music bonus.features

One formula to achieve this is:

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1); ", "; "|"); " "; "."); "|"; " "))

The first substitution replaces a comma and space (", ") with a character not likely to be found in cell A1: "|".

The second substitution replaces all spaces with periods.

The third substitution replaces all | characters with spaces.

The order is important so that wanted spaces aren't replaced with periods.

The TRIM() function ensures that no trailing spaces in A1 are converted to periods.

Another way to achieve this with fewer steps is to use this formula:

=LOWER(SUBSTITUTE(SUBSTITUTE(TRIM(A1); " "; "."); ",."; " "))

In this case, the first substitution replaces all the spaces with periods, which results in:

comedy,.music,.bonus.features

The second substitution replaces all instances of ",." with a space to get the desired result:

comedy music bonus.features