Need macro or code in excel to replace Special ASCII characters with English alplabets

Question

Can you please provide me macro or code in Excel to replace Special ASCII Characters into Alphabet.

Example, for À, Á, Â.. I need Alphabet as A.. and for ß, Ɓ, Ƅ.. I need Alphabet as B.

Can I replace these special characters with correct English alphabet characters?

Though it's not a VBA solution, [this SO answer](http://stackoverflow.com/a/249126/4717755) describes the process. Basically "[t]he approach uses String.Normalize to split the input string into constituent glyphs (basically separating the "base" characters from the diacritics) and then scans the result and retains only the base characters. It's just a little complicated, but really you're looking at a complicated problem." Also look at [How to replace accented characters...](https://www.extendoffice.com/documents/excel/707-excel-replace-accented-characters.html#a1). — PeterT, Oct 20 '16 at 13:47
As an aside, is that first B a `beta` or an `eszett` - i.e. a German double S? — Skip Intro, Oct 20 '16 at 14:18

score 0 · Answer 1 · answered Oct 20 '16 at 14:33

Say the data is in a worksheet called Data.

In a separate worksheet, called Sheet1, we place a small standalone Translate Table. I start the table at cell K1:

The table gives the code for each character we want to replace and the replacement character code.

We then run this short macro to perform the repalcements:

Sub FixAlphabets()
    Dim i As Long
    ary = Sheets("Sheet1").Range("K1").CurrentRegion
    For i = LBound(ary, 1) To UBound(ary, 1)
        Sheets("Data").Cells.Replace what:=ChrW(ary(i, 1)), replacement:=ChrW(ary(i, 2))
    Next i
End Sub

score 0 · Answer 2 · answered Sep 17 '17 at 16:04

As ß is not equivalent to B then it would be hard to code this without doing it for each possible change

You could code all of the possibilities doing a find and replace.

for example select the whole sheet and then find and replace

Cells.Select
ActiveCell.Replace What:="À", Replacement:="A"
ActiveCell.Replace What:="Á", Replacement:="A"
ActiveCell.Replace What:="Â", Replacement:="A"

and so on through any changes you will come across.

Need macro or code in excel to replace Special ASCII characters with English alplabets

2 Answers2