# Replace accented characters using regular expression

#### Luthius

##### Active Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Trevor G

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

VBA Code:
``````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

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

#### Trevor G

##### Well-known Member
By using substitute, the formula would be, but then you are looking to concatenating the formula multiple times

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

#### Luthius

##### Active Member

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

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

VBA Code:
``````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
AB
1Examples:
2AñaAna
3ÐiñeroDinero
4MåçãžMacaz
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=DeAccent(A2)

#### Luthius

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

#### Peter_SSs

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

Replies
5
Views
249
Replies
14
Views
487
Replies
2
Views
929
Replies
1
Views
152
Replies
9
Views
184

1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back