Do you know any formula to delete accents?
 

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

Answer: Do you know any formula to delete accents?
 

Yes, there is a formula that can help you remove accents from text in Excel. Here's how you can do it:

1. First, select the cell or range of cells that contain the text with accents that you want to remove.
2. Next, click on the "Formulas" tab in the Excel ribbon and select "More Functions" "Text" "UNICODE".
3. In the "UNICODE" dialog box, enter the cell reference of the first cell that contains the text with accents that you want to remove. For example, if the text is in cell A1, enter
   Code:

   ---

   "A1"

   ---

   in the "Number" field.
4. Click "OK" to close the dialog box. This will return the Unicode value of the first character in the text string.
5. Now, create a new column next to the column that contains the text with accents. In the first cell of the new column, enter the following formula:
   
   
   Code:

   ---

   =CHAR(IF(UNICODE(A1)<128,UNICODE(A1),32))

   ---

   This formula will check if the Unicode value of the character in cell A1 is less than 128 (which means it's a standard ASCII character), and if so, it will return the character. If the Unicode value is greater than 128 (which means it's a character with an accent), it will return a space.
6. Copy the formula down to the rest of the cells in the new column. This will remove the accents from all the text in the original column.
7. Finally, you can copy and paste the values from the new column back into the original column to replace the text with accents.

Do you know any formula to delete accents?
 

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.
--
Gary''s Student - gsnu200812


"Eduardo Pinto" wrote:

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

Do you know any formula to delete accents?
 

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

On Nov 10, 2:52*pm, Eduardo Pinto <Eduardo
wrote:
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

Do you know any formula to delete accents?
 

On Nov 10, 7:52*am, Eduardo Pinto <Eduardo
wrote:
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

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

Do you know any formula to delete accents?
 

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

"Pete_UK" wrote:

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

On Nov 10, 2:52 pm, Eduardo Pinto <Eduardo
wrote:
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

Do you know any formula to delete accents?
 

Hi Gary,

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

Thanks
regards
Eduardo

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200812


"Eduardo Pinto" wrote:

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

Do you know any formula to delete accents?
 

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!

Eduardo Pinto wrote:
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

Do you know any formula to delete accents?
 

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

On Nov 10, 4:59*pm, Eduardo Pinto
wrote:
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



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

On Nov 10, 2:52 pm, Eduardo Pinto <Eduardo
wrote:
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- Hide quoted text -

- Show quoted text -