Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Delete Foreign Characters (Excel 2002)

    Hi,
    We have a company credit card user that is located in Mexico and some of his expense narratives have odd/foreign characters in them. These odd characters are then upload into our system and are causing problems. I'd like to write a macro to delete these odd characters from the Excel database. I've never written a macro based on a "find" function... likewise, I tried to copy the odd characters from the Excel spreadsheet into Visual Basic and it didn't copy it... instead of the odd character being pasted, the word "print" showed up??? Anyway, below are the odd characters that are downloaded from our bank (into an Excel spreadsheet) and then uploaded into our system. I need help with the find function, as well as how would I type in these characters... plus, I'm guessing there are probably other odd characters that may show up in the future, so is there a way to tell the macro to search the data for ANY odd characters, not just these three?
    Thanks so much!!
    Lana




  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Delete Foreign Characters (Excel 2002

    Hi Lana

    Any chance you can do a screen dump of the chracters as I am getting characters below and I cannot get an ASCII value!!
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    I'm not sure how to do a screen dump... instead I attached an Excel file with the odd characters.
    Thanks!
    Lana
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002)

    Try this:

    Sub RemoveAliens()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants).Cells
    oCell = Replace(oCell, Chr(168), "")
    oCell = Replace(oCell, Chr(213), "")
    oCell = Replace(oCell, Chr(220), "")
    Next oCell
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    Oops, I hadn't seen your attachment when I posted my previous reply. Try this instead:

    Sub RemoveStrangeChars()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants).Cells
    oCell = Replace(oCell, ChrW(920), "")
    oCell = Replace(oCell, ChrW(966), "")
    oCell = Replace(oCell, ChrW(8804), "")
    Next oCell
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    This worked perfect! Is there a character listing out there... I'm assuming you identified the characters and matched them with a number ChrW(920)?? I'm concerned that these 3 particular characters won't be the only ones we encounter in the future. Any ideas on how to "clean" the data to get rid of ANY odd character?
    Thanks Hans!!
    Lana

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    I selected a cell in the 'charc' sheet, then switched to the Visual Basic Editor, and typed

    ? AscW(ActiveCell)

    in the Immediate window and pressed Enter. The number returned is the unicode character code.

    Here is a macro that will ruthlessly remove any "strange" characters:

    Sub RemoveOddChars()
    Dim oCell As Range
    Dim strOld As String
    Dim strNew As String
    Dim i As Integer
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants).Cells
    strOld = oCell
    strNew = ""
    For i = 1 To Len(strOld)
    If AscW(Mid(strOld, i, 1)) < 128 Then
    strNew = strNew & Mid(strOld, i, 1)
    End If
    oCell = strNew
    Next i
    Next oCell
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    This is awesome info to know... thanks so much for the explanation. I tried the new macro, and it is having a debug issue with the following line:
    If AscW(Mid(strOld, i, 1)) < 128 Then
    I tried it on the Excel spreadsheet that was attached in my second reply, and it didn't work. Any ideas?
    Thanks so much!!!
    Lana

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    What kind of "debug issue"? Do you get an error message? If so, what does it say?

    I have attached your workbook with my macro inserted. You can run it by clicking the command button.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    I ran it on the spreadsheet you attached with no problem....

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    I figured out what happened... I had copied and pasted the macro Hans wrote from my email... the emailed version had that line as follows:
    If AscW(Mid(strOld, i, 1)) < 128 Then
    I changed the < to the < symbol so that it would match the code Hans wrote in the Lounge, and then it worked... I'm thinking the email version must have converted the "less then" symbol to be < or something. But it works awesome now... thanks so much Hans!!! And thanks to Jerry & Steve for your responses as well!! I appreciate the help!
    Lana

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Foreign Characters (Excel 2002

    For others reading this: a character such as < may be displayed as <code>&lt;</code> in the e-mail notification sent out by the Lounge. That (i.e. <code>&lt;</code>) will not work in VBA, however.
    So it is always a good idea to check the post in the Lounge itself.

Posting Permissions

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