Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    5
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Exclamation Need help with using Mutipule langages in excel 2010 and how to get rid of nonsense charaters

    Hi I am trying to create a music catalog with a CSV that contains multiple languages (English, French, Spanish, Dutch, German, etc.) and the original CSV has the proper accents and syntax of the non English words. So when I import the CSV into Excel those accented charaters come up as nonsense ie:

    Zhané

    or

    Ca fait mal et ça fait rien

    etc.

    I was wondering if there was a way to get excel to read and render the characters properly without having to go and edit every individual cell?

    Image:
    Snap 2014-11-21 at 23.19.32.jpg

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Damien,

    Can you post your .csv file so we can do some testing? My initial thought is to clean the data with the clean function prior to importing

    On a side note:
    While researching this topic, I found a clever bit of code by Santosh (Stack OverFlow) which will translate cell values from one language to another. Select the cells to translate then run the code. Somewhat on the slow side because it is opening Internet Explorer but it works well.

    Translation.png

    Some Slight modifications
    Code:
    Public Sub TranslateTitle()
    'SELECT THE CELLS THEN RUN THE CODE
    Dim cell As Range
    Dim rng As Range
    For Each cell In Selection
        cell.Offset(0, 1) = Translation(cell)
    Next cell
    End Sub
    
    
    Function Translation(str) As String
    '-----------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim IE As Object, i As Long
        Dim inputstring As String, outputstring As String, text_to_convert As String, result_data As String, CLEAN_DATA
        Set IE = CreateObject("InternetExplorer.application")
    '-----------------------------------------------------
    'CHOOSE INPUT AND OUTPUT LANGUAGES
        inputstring = "auto" 'CHOOSE INPUT LANGUAGE
        outputstring = "en" 'CHOOSE OUTPUT LANGUAGE (en=English, es=Spanish, fr=French, etc.)
    '-----------------------------------------------------
    'OPEN IE, SURF GOOGLE TRANSLATOR, AND TRANSLATE TEXT
        text_to_convert = str
        IE.Visible = False
        IE.navigate "http://translate.google.com/#" & inputstring & "/" & outputstring & "/" & text_to_convert
    '-----------------------------------------------------
    'WAIT UNITL PAGE IS LOADED
        Do Until IE.ReadyState = 4
            DoEvents
        Loop
        Application.Wait (Now + TimeValue("0:00:5"))
        Do Until IE.ReadyState = 4
            DoEvents
        Loop
    '-----------------------------------------------------
    'GET RESULTS FROM GOOGLE AND CLEAN DATA
        CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
        For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
            result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) - InStr(CLEAN_DATA(j), ">"))
        Next
    '-----------------------------------------------------
    'CLOSE INTERNET EXPLORER AND RETURN TRANSLATION
        IE.Quit
        Translation = result_data
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-22 at 00:59.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    DamienCena (2014-11-24)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Damien,

    The following code will cycle through each character in each cell in column A. If it detects a character that you specify a to strip, it will remove the character from the title and place the amended title in the adjacent cell. In the line:

    Case 169, 167

    add additional Ascii numbers for the characters from the Extended ASCII Character set that you wish to strip separated by commas as shown. Google "Extended ASCII Character set". Place in a standard module.

    HTH,
    Maud

    StripChars.png

    Code:
    Public Sub StripChars()
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim rng As Range
    Dim title As String
    Dim s As String
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & LastRow)
    title = ""
    '--------------------------------------------
    'CYLCLE THROUGH CELLS IN COL A
    For Each cell In rng
    '--------------------------------------------
    'CYLCLE THROUGH CHARACTERS IN CELL
        For I = 1 To Len(cell)
    '--------------------------------------------
    'FILTER OUT UNDESIREABLE CHARACTERS
            s = cell.Characters(I, 1).Text
            Select Case Asc(s)
                Case 169, 167
                    GoTo skip
                Case Else
    '--------------------------------------------
    'REBUILD TITLE WITH ACCEPTABLE CHARACTERS
                    title = title & cell.Characters(I, 1).Text
            End Select
    skip:
        Next I
    '--------------------------------------------
    'PLACE REBUILT TTILE IN ADJACENT CELL
        cell.Offset(0, 1) = title
        title = ""
    Next cell
    End Sub
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    DamienCena (2014-11-24)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Damien,

    You could also approach this with a UDF (User Defined Function). Paste the following code in a standard module. in cell B1, place the following code =Strip(A1) and copy down.

    HTH,
    Maud

    StripChars2.png

    Code:
    Public Function Strip(rng As Range) As String
    Dim title As String
    Dim s As String
    title = ""
    For I = 1 To Len(rng)
        s = rng.Characters(I, 1).Text
        Select Case Asc(s)
            Case 169, 167
                GoTo skip
            Case Else
                title = title & rng.Characters(I, 1).Text
        End Select
    skip:
    Next I
    Strip = title
    End Function

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    DamienCena (2014-11-24)

  8. #5
    New Lounger
    Join Date
    Nov 2014
    Posts
    5
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Damien,

    Can you post your .csv file so we can do some testing? My initial thought is to clean the data with the clean function prior to importing
    http://www.mediafire.com/view/asugoq...funcatalog.csv

  9. #6
    New Lounger
    Join Date
    Nov 2014
    Posts
    5
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Damien,

    The following code will cycle through each character in each cell in column A. If it detects a character that you specify a to strip, it will remove the character from the title and place the amended title in the adjacent cell. In the line:

    Case 169, 167

    add additional Ascii numbers for the characters from the Extended ASCII Character set that you wish to strip separated by commas as shown. Google "Extended ASCII Character set". Place in a standard module.

    HTH,
    Maud

    StripChars.png

    Code:
    Public Sub StripChars()
    '--------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim rng As Range
    Dim title As String
    Dim s As String
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & LastRow)
    title = ""
    '--------------------------------------------
    'CYLCLE THROUGH CELLS IN COL A
    For Each cell In rng
    '--------------------------------------------
    'CYLCLE THROUGH CHARACTERS IN CELL
        For I = 1 To Len(cell)
    '--------------------------------------------
    'FILTER OUT UNDESIREABLE CHARACTERS
            s = cell.Characters(I, 1).Text
            Select Case Asc(s)
                Case 169, 167
                    GoTo skip
                Case Else
    '--------------------------------------------
    'REBUILD TITLE WITH ACCEPTABLE CHARACTERS
                    title = title & cell.Characters(I, 1).Text
            End Select
    skip:
        Next I
    '--------------------------------------------
    'PLACE REBUILT TTILE IN ADJACENT CELL
        cell.Offset(0, 1) = title
        title = ""
    Next cell
    End Sub


    How do you use these codes sorry I a new to codes for Excel?

  10. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Damien,

    No apologies needed. We all start somewhere

    1. Open Excel and press Alt-F11 to bring up VBA interface

    2. Insert (on Menu) > Module

    3. Directly under General, paste code as directed in steps 4 or 5

    4. Post#3 (Subroutine): Paste Code > In Excel, click Developer Tab> Macros> StripChars > Run

    5. Post#4 (Function): Paste Code > In Excel, select Cell B1 > Enter formula =Strip(A1) (see image in post#4)

    If the Developer Tab is not visible in the ribbon then see Microsoft: How to show the Developer Tab

    To understand, read this quick tutorial: Starting with Visual Basic for Applications in Excel

    Please open my attached spreadsheets and press Alt-F11 > Modules to see where the code is placed in the VBA interface.

    If you upload you spreadsheet, I will place the code for you

    Maud

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    DamienCena (2014-11-24)

  12. #8
    New Lounger
    Join Date
    Nov 2014
    Posts
    5
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks s much Maud...

    I can't seem to get the attachment to attach here via upload or weblink via the attach function on the site so here is the direct link via mediafire

    http://www.mediafire.com/view/asugoq...funcatalog.csv

  13. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Damien,

    Here is your properly formatted list of songs/artists/year/etc.

    music.png

    Since it is 1.9Mb, it is too large to upload (18961 songs), I placed it in a zipped file.

    HTH,
    Maud
    Attached Files Attached Files

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    DamienCena (2014-11-24)

  15. #10
    New Lounger
    Join Date
    Nov 2014
    Posts
    5
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Maud you so rule my world!!!!


    Thank you so much for all your help!!


    Damien

    Quote Originally Posted by Maudibe View Post
    Damien,

    Here is your properly formatted list of songs/artists/year/etc.

    music.png

    Since it is 1.9Mb, it is too large to upload (18961 songs), I placed it in a zipped file.

    HTH,
    Maud

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..the file would be a tiny bit smaller if you got rid of all the double-quote marks in column [B].
    And if we were allowed to post Excel files here in the lounge in Excel's .xlsb file format, it would only be 586kb in size.

    zeddy

  17. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    You are absolutely correct. Since I did not know what version Damien has, I left it in .xls format. Even ,xlsx would have been smaller. To remove the quotes would reduce it even more as you stated but it many times it comes down to second guessing what the OP wants. Good suggestion though.

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2014-11-26)

  19. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..I was just trying to get some support for being able to post .xlsb files. Since 1.9MB -> 0.6MB is a big reduction in my book.
    Who can we ask to get .xlsb files posted here?????

    zeddy

  20. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    That would be a plus. I guess the first place to start would be with the forum moderator. Hopefully RG ("Request Grantor") may be able to help!

Posting Permissions

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