Replace accented characters using regular expression

[Luthius]

Guys how can I replace accented character to Non accented characters using regular expression REGEX?

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

Examples:
Aña -------> Ana
Ðiñero ----> Dinero
Måçãž-----> Macaz

I know I can achieve this by replacing each character using Mid() inside a loop, but I would like to solve this using RegExp.

 

[Trevor G]

The quickest way is to use code. If you want a longer method via formula then I would consider looking at Substitute.

Sub AccentChar()
'For each cell in each worksheet
'Look for each accent character and replace them
'Const AcentFd = Find Accent character
'Const AcentTd = Replace each Accent Character

Const AcentFd As String = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const AcentTd As String = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
Dim i As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    For i = 1 To Len(AcentFd)
        ws.Cells.Replace Mid(AcentFd, i, 1), Mid(AcentTd, i, 1), LookAt:=xlPart, MatchCase:=True
    Next i
Next ws
End Sub

 

[Luthius]

Thanks, but As mentioned before I want to achieve by using regular expressions

 

[Trevor G]

By using substitute, the formula would be, but then you are looking to concatenating the formula multiple times


=SUBSTITUTE(A1,"á","a")

 

[Luthius]

By using substitute, the formula would be, but then you are looking to concatenating the formula multiple times


=SUBSTITUTE(A1,"á","a")

Lets say you are working with an amount of data and want to do avoid these special characters be inserted in your dataset. One way is generating a routine that does it by treating the data before insert it. There are different approaches to accomplish it but I want to try by using Regex because the feature/library already exist and it's well known by programmers. So instead creating loops a simple solution with regex would be fine.

 

[Peter_SSs]

You will still need some looping even with RegEx but it could/should be less. You could try this user-defined function.

Function DeAccent(s As String) As String
  Dim RX As Object, itm As Object
  
  Const sAccents As String = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
  Const sNoAccents As String = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(.)"
  RX.Pattern = Mid(RX.Replace(sAccents, "|$1"), 2)
  For Each itm In RX.Execute(s)
    s = Replace(s, itm, Mid(sNoAccents, InStr(1, sAccents, itm, 0), 1))
  Next itm
  DeAccent = s
End Function

Luthius 2020-07-20 1.xlsm
	A	B
1	Examples:
2	Aña	Ana
3	Ðiñero	Dinero
4	Måçãž	Macaz
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=DeAccent(A2)

 

[Luthius]

Thanks but it uses both LOOP and MID. Anyway, thank you guys. I thought using REGEX a loop would not be necessary.
.

 

[Peter_SSs]

Since there are multiple characters to search for and they require different replacements, there is no way to do them all at once.