Converting to ASCII Text

I checked the comments to gather helpful info and noticed some opening comments stating:

"The macro in this tip is really sloppy work" "Obviously..." "a dozen of useless characters" "my version of the macro (smaler as usual :-) that does the job properly"

While the opinions may be true, does the opening sentence often set a "tone" and "expectation" for the reader?

Because for me, I think of conflict, attacks and argumentative posturing however all I really want is well presented information.

Please make your points more inclusive to allow many audiences to share and learn from them... even for timid, uneducated readers like myself?

Respectfully, BJT

I'm perplexed! The text of this tip starts with (last updated June 19, 2018) but Allen or his co-worker didn't take any notice of my comment of 26 Aug 2017 reporting a severe bug in the macro, it still fails to work.

Today i got a "Daily Nugget" referring to this tip with still the cripple macro... That's bad publicity !

@Ru Lopi
If you want to do it with a UDF this smaler function will do it much faster since it has only one loop:

Function StripAccent(sText As String) As String
Dim X As Integer, Y As Integer
Const sAcc = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÓÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const sReg = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For X = 1 To Len(sText)
Y = InStr(sAcc, Mid(sText, X, 1))
If Y Then Mid(sText, X, 1) = Mid(sReg, Y, 1)
Next X
StripAccent = sText
End Function

The following will replace foreign characters in a cell.

In a module place this code:

Function funcStripAccent(strText As String) As String

Dim sAcc As String
Dim sReg As String

Dim intInner As Integer
Dim intOuter As Integer

sAcc = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÓÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
sReg = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"

For intOuter = 1 To Len(strText)

For intInner = 1 To Len(sAcc)

If Mid(strText, intOuter, 1) = Mid(sAcc, intInner, 1) Then
Mid(strText, intOuter, 1) = Mid(sReg, intInner, 1)
End If

Next intInner

Next intOuter

funcStripAccent = strText

End Function

Assuming that the foreign text is in A1 then in the destination cell enter: =funcStripAccent(A1)
This can be fllled to other cells as required.

As mentioned, sAcc and sReg must be the same length with matching conversion characters.

The macro in this tip is really sloppy work. When you copy and paste it in VBA the "sAcc = ..." row gets entirely blood red meaning there is an error. This is because at 2 places in the string there is a single quotation mark which is not allowed . When you remove them you can run the macro but it replaces all the characters in the selected text with an o (ooooooo...)!!!.

Obviously the sAcc and sReg strings should be exactly the same length which is not the case here. The sAcc string is much longer and contains beside the 2 quotation marks a dozen of useless characters including some question marks. These causes the erroneous result. The last question mark in the sAcc string corresponds to the letter o in the sReg string so when sA contains a ? , Replace considers this as a wild card and replaces all the characters in the selection with an o which is the content of sR at that moment.

Here is my version of the macro (smaler as usual :-) that does the job properly:

Sub StripAccent()
Const sAcc = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçéèêëìíîïðñòóôõöùúûüýÿ"
Const sReg = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeeiiionooooouuuuyy"
Dim X As Integer
For X = 1 To Len(sAcc)
Selection.Replace _
What:=Mid(sAcc, X, 1), _
Replacement:=Mid(sReg, X, 1), _
LookAt:=xlPart, MatchCase:=True
Next
End Sub