Microsoft Excel is one of the applications, or the most used, for managing large amounts of data since thanks to its different Excel formulas , functions and characteristics, they provide us with a complete set of options so that the data can be processed in the correct way . On many occasions, the data that we must work with in Excel are names with accents (tildes):
This is not really complex, but accents or accents can have a negative impact on the behavior of some actions to be executed in Excel such as Excel macros , scripts or other automation tasks. Therefore, there is an option to remove these accents in a simple way since doing it manually would take a lot of time, let's see how to achieve this..
To stay up to date, remember to subscribe to our YouTube channel! SUBSCRIBE
How to remove accents in Excel 2016 or 2019
Step 1
For this we must take into account the cell from where our data starts, in this case it will be cell A1, then in a blank cell we copy the following:
= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A1; "á"; "a"); "é"; "e"); "í"; "i"); "ó"; "o"); "ú"; "u"); "Á"; "A"); "É"; "E"); "Í"; "I"); "Ó"; " O ");" Ú ";" U ")
Note
Cell A1 must be replaced by the cell where the data starts.
Step 2
We have entered each letter with and without accents and in upper and lower case in order to fully encompass the data entry options.
When pressing Enter we will see that the cell takes the name of the selected cell, but without an accent:
Step 3
We drag this formula down by clicking in the lower right corner so that it applies to the other data in column A in this case:
Step 4
Now we drag the formula to the right for the other data in column B:
Step 5
We copy this data in order to replace the values with accents, when we paste them we go to the paste options and select "Values":
Step 6
As a result we have replaced the data with accents in Excel 2016 or 2019. We can delete the selected data if we wish.
With this simple formula we are able to eliminate accents in the data that have them in Excel 2016 or 2019.