There are some situations when we need to use, in MS Office applications (eg Excel), certain words without diacritics. In VBA we can do this relatively easily, using such a function (ExtendOffice):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Function StripAccent(thestring As String) Dim A As String * 1 Dim B As String * 1 Dim i As Integer Const AccChars= "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ" Const RegChars= "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy" For i = 1 To Len(AccChars) A = Mid(AccChars, i, 1) B = Mid(RegChars, i, 1) thestring = Replace(thestring, A, B) Next StripAccent = thestring End Function |
The problem is that, if we look carefully, we don’t really find the ones used in the Romanian language (“ş”, “ţ”, ă). If we want to enter them in VBA we will notice that they are automatically converted to other characters.
To avoid such stress, an elegant solution is to use ASCII codes. Because not all Romanian diacritics are part of the original ASCII set, we will resort to the extended Unicode character set. It helps to use Chr$ (for ASCII) and ChrW (for Unicode) functions.
Therefore, I allowed myself to adapt the code above, “romanizing” it and regarding the variable name, in order to be as understandable as possible their action.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Function diacrit(expresie As String) As String Dim litD As String ' diacritica Dim litS As String ' litera simpla Dim i As Integer Dim AsciiUnicode As String ' diacriticile vizate Dim litere As String ' literele corespondente diacriticilor ' Echivalente: ChrW(&H102) & ChrW(&H103) - Ăă ' Chr$(194) & Chr$(226) - Ââ ' Chr$(206) & Chr$(238) - Îî ' ChrW(&H218) & ChrW(&H219) - Șș, cu virgule, aferente tastaturii RO Standard ' ChrW(&H15E) & ChrW(&H15F) - Şş, cu sedile, aferente tastaturii RO Legacy ' ChrW(&H21A) & ChrW(&H21B) - Țț, cu virgule, aferente tastaturii RO Standard ' ChrW(&H162) & ChrW(&H163) - Ţţ, cu sedile, aferente tastaturii RO Legacy AsciiUnicode = ChrW(&H102) & ChrW(&H103) & _ Chr$(194) & Chr$(226) & _ Chr$(206) & Chr$(238) & _ ChrW(&H218) & ChrW(&H219) & ChrW(&H15E) & ChrW(&H15F) & _ ChrW(&H21A) & ChrW(&H21B) & ChrW(&H162) & ChrW(&H163) litere = "AaAaIiSsSsTtTt" For i = 1 To Len(AsciiUnicode) litD = Mid(AsciiUnicode, i, 1) litS = Mid(litere, i, 1) expresie = Replace(expresie, litD, litS) Next diacrit = expresie End Function |
Of course, if we are interested in other letters with different accents that we want to convert to, we can add them to those vectors.
If in Excel we can use this function, if we use Google Sheets, we can convert the function into compatible code. For this we should follow the steps:
- In the Extensions menu we choose Apps Script.
- In the Apps Script editor, weadd the code below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
function diacrit(expresie) { var litD; // diacritica var litS; // litera simpla var i; // diacriticile vizate var AsciiUnicode = String.fromCharCode(258) + String.fromCharCode(259) + String.fromCharCode(194) + String.fromCharCode(226) + String.fromCharCode(206) + String.fromCharCode(238) + String.fromCharCode(536) + String.fromCharCode(537) + String.fromCharCode(350) + String.fromCharCode(351) + String.fromCharCode(538) + String.fromCharCode(539) + String.fromCharCode(354) + String.fromCharCode(355); // literele corespondente diacriticilor var litere = "AaAaIiSsSsTtTt"; for (i = 0; i < AsciiUnicode.length; i++) { litD = AsciiUnicode.charAt(i); litS = litere.charAt(i); expresie = expresie.split(litD).join(litS); } return expresie; } |
- We save the script and project.
- In Google Sheets, we will write like this: =diacrit("B2")
