Removing accents from characters in strings

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
 
E

Earl Kiosterud

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.
 
D

Dave Peterson

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
 
Top