Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

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.