Replacing regular and special characters

**dandi10** · 05-31-2022, 03:01 AM

Hi there,

I need a macro or any other way to replace regular and special characters in excel. I have this macro but only the last line is working. Any ideas? Thanks!

Sub Special_Characters()
'
' Special_Characters Macro
Range("A1:ZZ200000").Replace What:="á", Replacement:="a", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="¯", Replacement:="a", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="¦", Replacement:="i", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="ñ", Replacement:="n", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="ó", Replacement:="o", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="Ñ", Replacement:="N", LookAt:=xlWhole
Range("A1:ZZ200000").Replace What:="í", Replacement:="i", LookAt:=xlWhole
Range("i1:i500").Replace What:="pr", Replacement:="US", LookAt:=xlWhole

End Sub

**sintek** · 05-31-2022, 03:07 AM

Please see yellow banner above regarding sample file upload showing before | after expectation...

**TMS** · 05-31-2022, 03:15 AM

á 135 =CODE(A1)
a 97

Range("A1:ZZ200000").Replace What:="á", Replacement:="a", LookAt:=xlWhole 135 =CODE(MID(A4,37,1)) 97 =CODE(MID(A4,55,1))
Range("A1:ZZ200000").Replace What:="¯", Replacement:="a", LookAt:=xlWhole 248 .. .. .. .. .. .. .. .. .. .. .. .. 97 .. .. .. .. .. .. .. .. .. .. .. ..
Range("A1:ZZ200000").Replace What:="¦", Replacement:="i", LookAt:=xlWhole 95 .. .. .. .. .. .. .. .. .. .. .. .. 105 .. .. .. .. .. .. .. .. .. .. .. ..
Range("A1:ZZ200000").Replace What:="ñ", Replacement:="n", LookAt:=xlWhole 150 .. .. .. .. .. .. .. .. .. .. .. .. 110 .. .. .. .. .. .. .. .. .. .. .. ..
Range("A1:ZZ200000").Replace What:="ó", Replacement:="o", LookAt:=xlWhole 151 .. .. .. .. .. .. .. .. .. .. .. .. 111 .. .. .. .. .. .. .. .. .. .. .. ..
Range("A1:ZZ200000").Replace What:="Ñ", Replacement:="N", LookAt:=xlWhole 132 .. .. .. .. .. .. .. .. .. .. .. .. 78 .. .. .. .. .. .. .. .. .. .. .. ..
Range("A1:ZZ200000").Replace What:="í", Replacement:="i", LookAt:=xlWhole 146 .. .. .. .. .. .. .. .. .. .. .. .. 105 .. .. .. .. .. .. .. .. .. .. .. ..

Range("A1:ZZ200000").Replace What:=Chr(135), Replacement:="a", LookAt:=xlWhole

**dandi10** · 05-31-2022, 10:49 AM

Thank you but sorry, can you help in explain your reply?

**sintek** · 05-31-2022, 10:50 AM

can you help in explain your reply?

My reply says...Add a sample file...

**TMS** · 05-31-2022, 12:24 PM

What's to explain?

Formula:

Please Login or Register  to view this content.

Instead of:

Formula:

Please Login or Register  to view this content.

The other examples show the codes to use.

Replacing regular and special characters

LinkBack

Thread Tools

Rate This Thread

Display

Replacing regular and special characters

Re: Replacing regular and special characters

Re: Replacing regular and special characters

Re: Replacing regular and special characters

Re: Replacing regular and special characters

Re: Replacing regular and special characters

Thread Information

Users Browsing this Thread

Similar Threads

Replacing Special Characters

[SOLVED] Regular expression with Cyrillic characters

[SOLVED] Replace accented characters in worksheet name to regular characters

Find & Replace all special characters with regular

[SOLVED] Macro to replace special characters, keep list of replacements or confirm before replacing

[SOLVED] Regex Replacing Formulas: Issue with Special Characters

Replacing Special Characters when importing text

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions