Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

This works for me:

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

2 substitutions

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

3 substitutions

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

=SUBSTITUTE(SUBSTITUTE(yourcell; CAR(231); CAR(99)); CAR(251); CAR(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; CAR(46); "") 
share|improve this answer
    
So, for 10 non-English characters I would need 10 nested SUBSTITUTE() calls? As I stated in the question, that's exactly what I was trying to avoid. Sorry if I missed something in your response. – user1566515 Sep 16 '13 at 19:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .