=SUBSTITUTE(A1,B1, "~") ignore accents?

[Windy Skies]

Hi,

I have the two left columns. Note the accents in the columns, which are often not the same between columns 1 & 2. (I could delete the accents in column 1 but not column 2). My desired result is in column 3. Is this possible?

aìyīnsītǎn	tā zìrán búshì àiyīnsītǎn.	tā zìrán búshì ~.
àibùshìshǒu	tā duì nàtái bǐjìběndiànnǎo àibúshìshǒu.	tā duì nàtái bǐjìběndiànnǎo ~.

<tbody>
</tbody>

 

[Rick Rothstein]

Please clarify... are you always replacing the LAST word with the tilde symbol as your two examples show?

 

[Windy Skies]

Nope. Thanks for your reply.

 

[Rick Rothstein]

Nope. Thanks for your reply.

Okay then, are you replacing just the word you are looking up with the tilde leaving the text following it (when there is text following it) alone or are you replacing all text from the word you are looking up to the end of the text with the tilde?

 

[Windy Skies]

Some other examples (without the accents):

airen	wo de airen zai jia	wo de ~ zai jia
wo	ta he wo bu zai jia	ta he ~ bu zai jia

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Column 1 is a word that can occur anywhere in column 2 (an example sentence). I would like to replace only the word, nothing else.

 

[Windy Skies]

...or are you replacing all text from the word you are looking up to the end of the text with the tilde?

...or would you have a method if that was the case? Maybe it is then possible to achieve what I want in a few more steps.

 

[Scott Huish]

I think the op is asking for something like this:
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:89px;" /><col style="width:226px;" /><col style="width:267px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >aìyinsitan</td><td >ta zìrán búshì àiyinsitan</td><td >ta zìrán búshì ~</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

The word has to be replaced even though the accent marks don't match. Is this what you are after?

 

[Windy Skies]

Yes

And if need be I could delete the accents from the first column.

 

[Scott Huish]

I think it will take a little VBA to do this, is there any way you can supply a list of all the characters that would have these accents?

 

[Windy Skies]

Thanks for your reply.

I think this is all of them. If you're interested, these are the 5 tones of Chinese pinyin (the 5th is neutral and without a mark).

āáǎàa
ēéěèe
īíǐìi
ōóǒòo
ūúǔùu
ǖǘǚǜü

The file has some 40k rows.