Today I will give you a solution to a problem that can affect some of my non English language users.
I’m a native Hungarian and as such, our alphabet includes some funny letters with Hungarian accents on it.
They are essential to the Hungarian language but sometimes we need to remove the accents to prevent issues in named ranges, etc…
The code is not too crazy, I will try to explain below how it works.
How can we remove accents anyway?
The old school way to remove accents: (Non VBA and painful)
We could use Ctrl+H. The built in Replace function of Microsoft Excel.
We could go through all the letters we want to replace with their non accented counterpart. although we can all agree that this would be a painstaking job.
A much better solution would be to remove the Accented characters with the help of some VBA magic.
The code I got here is customisable so you not just stuck say Spanish, or French character sets.
You can customise the code for your language.
How to use this code?
- Copy the code and paste it in to a module in your workbook.
- Modify the two lines of letters to your need
(What you want to replace to what)
- Go back to your worksheet and start typing =STRIPACCENT
- You will see a new formula (the one we just created with the custom function)
- In the formula all you need is a single cell
(your target cell that contains the accented characters)
- Hit enter and you will get the results which is the cell’s value with the accents stripped
CODE TO REPLACE ACCENTED CHARACTERS IN EXCEL VBA
Function STRIPACCENT(thestring As String) Dim A As String * 1 Dim B As String * 1 Dim i As Integer Const AccChars = "ÖÜÓOÚÉÁUöüóoúéáu" '<<<This is what you want to replace Const RegChars = "OUOOUEAUouooueau" '<<<This is what to replace to 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
You can drag this formula on your worksheet any way you want it.
THINGS TO NOTE:
- In the code above you need to put the letters in exactly to the same place as the one you want to replace
- The code will loop through the characters and if it founds domething in the 3rd place it will replace it with theletter that is in the same place below.
- To use this function constantly you need to save your workbook in an excel macro-enabled format ie. xlsm. If you need to function to be available all the time, then you need to place the code in to your Personal workbook (hidden in excel)
Come and visit us.
Come and visit excelangel on Facebook to find and join the growing community of true office workers.
Oh, I almost forgot.
Drop us a like while you there…