Remove Unwanted Characters

  • Hi,


    Please help, this is freaking me out. i want to convert an excel spreadsheet into a text file, keeping the same format, but when i do so, excel puts " " around the characters, which i don't want. Example: please see the 2 attachments.


    e.g. when i convert, i don't want the " " around the commas in the text file.


    any help would be greatly appreciated. thanks.

  • Re: Unwanted Characters Converting To Text Format


    This is to do basically with CSV. Excel is making it safe to import into another program as comma's unless enclosed in quotes tend to be used as field delimiters. This means that if there is a comma in a text string, unless it is in quotes, it will be split into two fields at the comma..


    It seems to do that for all the text conversions, which is a bit naff.....


    Regards,


    Rich

  • Re: Unwanted Characters Converting To Text Format


    Thanks for the response, Rich z.


    But can you please explain on how to save a excel file into a text or csv file with excel putting those extra commas in?


    so instead of "," in my file, i want ,


    i can't import that file into another program with the " " around the comma as the program doesn't recognise it.


    i did this before, but now it is not working, could it be because i installed office updates? i'm lost....

  • Re: Unwanted Characters Converting To Text Format


    aaaah, yeah, thanks alot HTH... i can't believe i didn't think of it, but i will next time... thanks again... :p

  • Re: Unwanted Characters Converting To Text Format


    Quote from douglash

    aaaah, yeah, thanks alot HTH... i can't believe i didn't think of it, but i will next time... thanks again... :p


    Unless one of the guys who know's Excel better than me can suggest a better way, I would think you would need a macro to do it, or if you're not up to writing that, and transferring the file is not a regular occurance, then Ctrl-H I'm afraid.


    What option are you using to convert the file ? CSV, TAB, TXT, MSDOS TXT ??


    Regards


    Rich

  • Re: Remove Unwanted Characters



    Please use this code.



    Sub removeunwantedchart()
    Dim a As Object
    Dim rng As Range
    Set a = CreateObject("VBScript.RegExp")
    With a
    .Global = True
    .Pattern = "[^a-zA-Z0-9]"
    For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    rng.Value = .Replace(rng.Value, "")
    Next rng
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!