Removing accents from characters in strings

  • Thread starter Thread starter grinning_crow
  • Start date Start date
G

grinning_crow

I have two lists of data which need to be merged only the common colum
has accents on the letters on one sheet and none on the other, i.e.:

Aceguá in one and Acegua in the other.

Can someone tell me how to remove accents from these characters withou
using the find-replace method (there are quite a few different ones an
I'd rather avoid using that method if possible).

Thanks for your help
 
Crow,

Removing that accent (it's actually called an acute accent, though it's not
any cuter than the grave (à)) does in involve changing the character from á
(code 225) to a (code 97). Any method of changing them would essentially be
a search and replace. If no one comes forth soon with a tool to do that, we
can write you a macro that will rip down your column and change them.
 
Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

If you have to do this lots of times, I'd record a macro when you do an
Edit|Replace and modify it to do those funny characters:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(255), Chr(123))
myGoodChars = Array(Chr(97), Chr(123))

If UBound(myGoodChars) = UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

Just keep adding those Hex codes you found using Chip's addin.

You could use:
mybadchars= array("á", ...
if you can type/copy&paste them)



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top