Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?


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:


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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.