+3 votes
16 views
Remove accents in Excel 2019 and Excel 2016 | Formula

in Office by (304k points)
reopened | 16 views

1 Answer

+4 votes
Best answer

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):

 

image

 

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:

 

image

 

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:

 

image

 

 

Step 4

Now we drag the formula to the right for the other data in column B:

 

image

 

 

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":

 

image

 

 

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.

 

 

image

 

With this simple formula we are able to eliminate accents in the data that have them in Excel 2016 or 2019.

 


by (1.9m points)

Related questions

+3 votes
1 answer
+3 votes
1 answer
+5 votes
1 answer
+3 votes
1 answer
+4 votes
1 answer
asked Sep 16, 2019 in Office by backtothefuture (304k points) | 74 views
Please leave a comment about whether the solution works or not (with device model)   [X]Close
5,795 questions
5,885 answers
164 comments
2 users