Replace accented characters using regular expression

Luthius

Active Member
Joined
Apr 5, 2011
Messages
315
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 5, 2011
Messages
315
Thanks, but As mentioned before I want to achieve by using regular expressions
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
By using substitute, the formula would be, but then you are looking to concatenating the formula multiple times


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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
315

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 5, 2011
Messages
315
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
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Since there are multiple characters to search for and they require different replacements, there is no way to do them all at once.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top