=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]

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.

Assuming those characters came through perfectly, then this UDF (user defined function) should work correctly...

Function RemoveAccentedWord(WordToRemove As Range, TextToRemoveItFrom As Range) As String
  Dim X As Long, Position As Long, Accents As Variant, WTR As String, TTR As String
  If Len(WordToRemove) * Len(TextToRemoveItFrom) = 0 Then Exit Function
  Accents = " 257 225 462 224 275 233 283 232 299 237 464 236" & _
            " 333 243 466 242 363 250 468 249 470 472 474 476 252 "
  For X = 1 To Len(WordToRemove)
    Position = InStr(Accents, " " & AscW(Mid(WordToRemove, X, 1)) & " ")
    If Position Then
      WTR = WTR & Mid("aeiouuu", 1 + Int((Position - 1) / 16), 1)
    Else
      WTR = WTR & Mid(WordToRemove, X, 1)
    End If
  Next
  For X = 1 To Len(TextToRemoveItFrom)
    Position = InStr(Accents, " " & AscW(Mid(TextToRemoveItFrom, X, 1)) & " ")
    If Position Then
      TTR = TTR & Mid("aeiouuu", 1 + Int((Position - 1) / 16), 1)
    Else
      TTR = TTR & Mid(TextToRemoveItFrom, X, 1)
    End If
  Next
  RemoveAccentedWord = WorksheetFunction.Replace(TextToRemoveItFrom, InStr(TTR, WTR), Len(WTR), "~")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RemoveAccentedWord just like it was a built-in Excel function. For example, assuming A1 contains the word you want to replace and B1 contains the text you want to replace it in, then...

=RemoveAccentedWord(A1,B1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Windy Skies]

Thanks! The function seems to execute correctly. The result doesn't display for me though:

I have saved this test workbook as .xlsm and enabled macros. I closed and opened the workbook as well.

Also I see you have used:
"aeiouuu" it shouldn't be "aeiouü"? note you have used 3 "u" and I used one "u" and one "ü" (u with a diaeresis). ü is not a frequent character.

 

[Windy Skies]

I pasted it now into a different worksheet. I originally pasted it into the sheet "Macro1" which I guess was created automatically and I didn't notice. It now works. Thanks! If it doesn't find a match it returns "#VALUE!" which is okay.

 

[Rick Rothstein]

Also I see you have used:
"aeiouuu" it shouldn't be "aeiouü"? note you have used 3 "u" and I used one "u" and one "ü" (u with a diaeresis). ü is not a frequent character.

The three u's were on purpose and should remain exactly that way... it has to do with how the characters are counted and translated for the comparison (you had 4 accented characters for a, e, i and o and 9 accented characters for u... so I did the counts by fours meaning I needed 3 u's to handle the two full sets of 4 plus another one for the 9th u).

I pasted it now into a different worksheet. I originally pasted it into the sheet "Macro1" which I guess was created automatically and I didn't notice. It now works. Thanks! If it doesn't find a match it returns "#VALUE!" which is okay.

This modification to the code I gave you earlier should stop the #VALUE! errors (note... I put the three u's back)...

Function RemoveAccentedWord(WordToRemove As Range, TextToRemoveItFrom As Range) As String
  Dim X As Long, Position As Long, Accents As Variant, WTR As String, TTR As String
  If Len(WordToRemove) * Len(TextToRemoveItFrom) = 0 Then Exit Function
  Accents = " 257 225 462 224 275 233 283 232 299 237 464 236" & _
            " 333 243 466 242 363 250 468 249 470 472 474 476 252 "
  For X = 1 To Len(WordToRemove)
    Position = InStr(Accents, " " & AscW(Mid(WordToRemove, X, 1)) & " ")
    If Position Then
      WTR = WTR & Mid("aeiouuu", 1 + Int((Position - 1) / 16), 1)
    Else
      WTR = WTR & Mid(WordToRemove, X, 1)
    End If
  Next
  For X = 1 To Len(TextToRemoveItFrom)
    Position = InStr(Accents, " " & AscW(Mid(TextToRemoveItFrom, X, 1)) & " ")
    If Position Then
      TTR = TTR & Mid("aeiouuu", 1 + Int((Position - 1) / 16), 1)
    Else
      TTR = TTR & Mid(TextToRemoveItFrom, X, 1)
    End If
  Next
  X = InStr(TTR, WTR)
  If X Then RemoveAccentedWord = WorksheetFunction.Replace(TextToRemoveItFrom, X, Len(WTR), "~")
End Function

 

[Windy Skies]

Thanks! I hadn't actually changed the code, just wanted to make sure that was intentional.