replacing accented characters with non accented equivalents

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: replacing accented characters with non accented equivalents

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default replacing accented characters with non accented equivalents

    Having diligently uploaded a large file of records to an email database I notice that accented characters are causing lots of problems.

    Has anyone some kind of automated method of changing "Lpez" with an accented "o" into "Lopez" without.

    i want to do this for several spanish characters.

    Paul

  2. #2
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Paulos,

    I have used the below Function to replace characters for e-mail addresses.

    Press and hold down the 'ALT' key, and press the 'F11' key.

    Insert a Module in your VBAProject, Microsoft Excel Objects

    Copy the below code, and paste it into the Module1.

    Code:
    Private Function CheckStringCHAR(InString) As String
    
    ' CheckStringCHAR(InString)
    ' Returns its passed agrument, but with exchanged European? characters
    ' Function created 7/08/2003 by Stanley D. Grom, Jr.
    '
    CheckStringCHAR = ""
    StringLength = Len(InString)
    
    For i = 1 To StringLength
        
        SearchCHAR = Mid(InString, i, 1)
        
        Select Case SearchCHAR
            Case ""                ' 138
                FoundCHAR = "S"
            Case ""                ' 142
                FoundCHAR = "Z"
            Case ""                ' 154
                FoundCHAR = "s"
            Case ""                ' 158
                FoundCHAR = "z"
            Case ""                ' 159
                FoundCHAR = "Y"
            Case ""                ' 192
                FoundCHAR = "A"
            Case ""                ' 193
                FoundCHAR = "A"
            Case ""                ' 194
                FoundCHAR = "A"
            Case ""                ' 195
                FoundCHAR = "A"
            Case ""                ' 196
                FoundCHAR = "A"
            Case ""                ' 197
                FoundCHAR = "A"
            Case ""                ' 199
                FoundCHAR = "C"
            Case ""                ' 200
                FoundCHAR = "E"
            Case ""                ' 201
                FoundCHAR = "E"
            Case ""                ' 202
                FoundCHAR = "E"
            Case ""                ' 203
                FoundCHAR = "E"
            Case ""                ' 204
                FoundCHAR = "I"
            Case ""                ' 205
                FoundCHAR = "I"
            Case ""                ' 206
                FoundCHAR = "I"
            Case ""                ' 207
                FoundCHAR = "I"
            Case ""                ' 209
                FoundCHAR = "N"
            Case ""                ' 210
                FoundCHAR = "O"
            Case ""                ' 211
                FoundCHAR = "O"
            Case ""                ' 212
                FoundCHAR = "O"
            Case ""                ' 213
                FoundCHAR = "O"
            Case ""                ' 214
                FoundCHAR = "O"
            Case ""                ' 217
                FoundCHAR = "U"
            Case ""                ' 218
                FoundCHAR = "U"
            Case ""                ' 219
                FoundCHAR = "U"
            Case ""                ' 220
                FoundCHAR = "U"
            Case ""                ' 221
                FoundCHAR = "Y"
            Case ""                ' 224
                FoundCHAR = "a"
            Case ""                ' 225
                FoundCHAR = "a"
            Case ""                ' 226
                FoundCHAR = "a"
            Case ""                ' 227
                FoundCHAR = "a"
            Case ""                ' 228
                FoundCHAR = "a"
            Case ""                ' 229
                FoundCHAR = "a"
            Case ""                ' 231
                FoundCHAR = "c"
            Case ""                ' 232
                FoundCHAR = "e"
            Case ""                ' 233
                FoundCHAR = "e"
            Case ""                ' 234
                FoundCHAR = "e"
            Case ""                ' 235
                FoundCHAR = "e"
            Case ""                ' 236
                FoundCHAR = "i"
            Case ""                ' 237
                FoundCHAR = ""
            Case ""                ' 238
                FoundCHAR = "i"
            Case ""                ' 239
                FoundCHAR = "i"
            Case ""                ' 240
                FoundCHAR = "o"
            Case ""                ' 241
                FoundCHAR = "n"
            Case ""                ' 242
                FoundCHAR = "o"
            Case ""                ' 243
                FoundCHAR = "o"
            Case ""                ' 244
                FoundCHAR = "o"
            Case ""                ' 245
                FoundCHAR = "o"
            Case ""                ' 246
                FoundCHAR = "o"
            Case ""                ' 249
                FoundCHAR = "u"
            Case ""                ' 250
                FoundCHAR = "u"
            Case ""                ' 251
                FoundCHAR = "u"
            Case ""                ' 252
                FoundCHAR = "u"
            Case ""                ' 253
                FoundCHAR = "y"
            Case ""                ' 255
                FoundCHAR = "y"
            Case Else
                FoundCHAR = SearchCHAR
        End Select
        
        CheckStringCHAR = CheckStringCHAR & FoundCHAR
        
    Next i
        
    End Function

    To use the function in your workbook you will need to go to a cell in a black column and copy the next formula in a cell. The "InString" in the formula should be replaced with the (same row):
    =CheckStringCHAR(InString)

    If your e-mail address is in cell A2, and the next available blank column is D. Then enter into cell D2 the following formula:
    =CheckStringCHAR(A2)

    All the European characters will be replaced by US characters.

    Have a great day,
    Stan

  3. #3
    New Member
    Join Date
    Oct 2006
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    :sehorn:

  4. #4
    Board Regular
    Join Date
    Sep 2008
    Posts
    505
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    This is what I use:
    Code:
     
    Sub acentos()
    Dim strFind As String
    Dim strReplace As String
    Dim i As Long
    strFind = ""
    strReplace = "aeiou"
    For i = 1 To Len(strFind)
        ActiveDocument.Range.Find.Execute FindText:=Mid$(strFind, i, 1), _
          ReplaceWith:=Mid$(strReplace, i, 1), Replace:=wdReplaceAll
    Next i
    End Sub

  5. #5
    New Member
    Join Date
    Oct 2006
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    Quote Originally Posted by Armando Montes View Post
    This is what I use:
    Code:
     
    Sub acentos()
    Dim strFind As String
    Dim strReplace As String
    Dim i As Long
    strFind = ""
    strReplace = "aeiou"
    For i = 1 To Len(strFind)
        ActiveDocument.Range.Find.Execute FindText:=Mid$(strFind, i, 1), _
          ReplaceWith:=Mid$(strReplace, i, 1), Replace:=wdReplaceAll
    Next i
    End Sub
    Not very solid with Excel, so how do you run this inside a spreadsheet?

  6. #6
    New Member
    Join Date
    Sep 2009
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    Or you could simply use the Find and replace function?

    "CTRL" and "f"

    Then copy the accented letter and paste it into the "find what" field and type the unaccented letter in the replace box

    Then click replace all

  7. #7
    New Member
    Join Date
    Oct 2006
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    Quote Originally Posted by MANNG99 View Post
    Or you could simply use the Find and replace function?

    "CTRL" and "f"

    Then copy the accented letter and paste it into the "find what" field and type the unaccented letter in the replace box

    Then click replace all
    When there are a lot of special characters, this is time consuming IMO...

  8. #8
    New Member
    Join Date
    Jul 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replacing accented characters with non accented equivalents

    Quite an old topic but well referenced on Google.
    Here's what I used :
    https://www.youtube.com/watch?v=UXnwu5cLD8I

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com