Do you know any formula to delete accents?

  • Thread starter Thread starter Eduardo Pinto
  • Start date Start date
E

Eduardo Pinto

I have a big database and to export I need to clean all accents, for example
the word Jerónimo I need it without accents Jeronimo, do you know any formula
to do that?
Thanks
Regards
 
Say you have text in column A and want each occurance of:
ó
with
o

In B1 enter:

=SUBSTITUTE(A1,CHAR(243),CHAR(111))
and copy down

Then copy column B and paste/special/values back onto column A

You must repeat the above for each letter of the alphabet whose accent you
want to remove.

It might be easier with a macro.
 
If Jerónimo is in A1, you can use this:

=SUBSTITUTE(A1,"ó","o")

You can have up to 7 levels of nesting in Excel 2003 and earlier, so
you can build up a more complex formula using other SUBSTITUTE
functions in a similar way, to get rid of other accents, and then copy
down, eg:

=SUBSTITUTE(SUBSTITUTE(A1,"ó","o"),"é","e")

will give you:

Jerónimo Jeronimo
Pépé Pepe

Hope this helps.

Pete
 
I have a big database and to export I need to clean all accents, for example
the word Jerónimo I need it without accents Jeronimo, do you know any formula
to do that?
Thanks
Regards
Click to expand...

Hi Eduardo, try this code:
Sub QuitarAcentos3()
With Cells
.Replace What:="á", Replacement:="a", MatchCase:=False
.Replace What:="é", Replacement:="e", MatchCase:=False
.Replace What:="í", Replacement:="i", MatchCase:=False
.Replace What:="ó", Replacement:="o", MatchCase:=False
.Replace What:="ú", Replacement:="u", MatchCase:=False
End With
End Sub
 
Ok it's a begin, but maybe we have one function to delete all type of accents
eg. ^,~,´,`
Thanks in advance for your help
Regards
Eduardo
 
Hi Gary,

With your formula doesn't work. How I can do a macro to solve this to all
accents eg. ~,^, `,´??

Thanks
regards
Eduardo
 
Eduardo,

It's not as easy as taking an eraser to the accent mark. Each character
that has an accent is just that, a single character. You'd have to
replace each character with another character.

I think amontes has a good idea, an you'd have to complete the list to
include any other accents you might want to change. The SUBSTITUTE
formula would work, as long as you can fit all the changes you need into
it,

G'Luck!
 
If you look at Character Map, you will see that there are many
possible accents, most of which will not be used in your language. The
formula I gave you will be limited to a maximum of 8 SUBSTITUTES (7
nested), so you could only change 8 accented letters (unless you used
further helper columns). If you wanted to do more, then a macro along
the lines of the one already suggested by amontes could be used.

Hope this helps,

Pete
 
Back
Top