+ Reply to Thread
Results 1 to 12 of 12

VBA Quotation Mark

  1. #1
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    678

    VBA Quotation Mark

    In VBA, we use """" for one quotation mark, it has not issue to display one quotation mark in Excel cell. But when we save the excel file as txt file, the one quotation mark turning to two quotation marks.

    Actually, I don't really care how it displays in Excel cell, how should I modify the code to show only one quotation mark in txt file (when saving the excel file to txt file)?

    Thanks.
    Last edited by VAer; 02-24-2017 at 09:36 AM.

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Quotation Mark

    May be this will help
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    678

    Re: VBA Quotation Mark

    Quote Originally Posted by kasan View Post
    May be this will help
    Please Login or Register  to view this content.
    Just tried. It also turns to 2 quotation marks when saving the excel file as txt file.

    Thanks.

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Quotation Mark

    How do you save your excel as Txt in your code? Using SaveAs?
    You could try write your data directly to txt file like this:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: VBA Quotation Mark

    How are you saving the file to text, and why are you adding quotation marks in the first place?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    678

    Re: VBA Quotation Mark

    @kasan @xlnitwit

    Manually save excel file as txt file. Maybe my question is: when saving excel file as txt file, if I need only one quotation mark in txt file, what character should appear in excel file (txt file comes from)?

    The reason is I need ONE quotation mark in TXT file, so I used """", but it does not act the way I want. I don't really need quotation mark in Excel cell, I need it because I need it for TXT file.
    Last edited by VAer; 02-24-2017 at 11:14 AM.

  7. #7
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    678

    Re: VBA Quotation Mark

    Quote Originally Posted by kasan View Post
    How do you save your excel as Txt in your code? Using SaveAs?
    You could try write your data directly to txt file like this:
    [/CODE]
    it is just complicated to write a TXT directly, since I need to deal with other information. Currently, if the Excel cell is ABC"OK"XYZ, I would like it also appear as ABC"OK"XYZ when saving the excel file as TXT file, not ABC""OK""XYZ . All I care is to have ABC"OK"XYZ in TXT file, it does not matter what it appears in Excel cell.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: VBA Quotation Mark

    Since Excel adds the quotes automatically, try not adding any yourself.

  9. #9
    Forum Contributor
    Join Date
    09-10-2016
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    678

    Re: VBA Quotation Mark

    Quote Originally Posted by xlnitwit View Post
    Since Excel adds the quotes automatically, try not adding any yourself.
    If I don't have quotation mark in excel, how can TXT file know where to add quotation mark?

  10. #10
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Quotation Mark

    In case we can't save file as .txt without double quotes - may be let excel save this txt file WITH double quotes and then some macro could read this text file and replace all "" to "

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA Quotation Mark

    here's an example that makes the variable e equal e = ABC"OK"XYZ

    Please Login or Register  to view this content.
    if writing a cell's value maybe use Replace(source_cell, "", """ & " & " & """)
    Last edited by Arkadi; 02-24-2017 at 06:30 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: VBA Quotation Mark

    In the absence of anything else, I would advocate outputting a single quote, saving the file as TEXT, and then do a global replace of a single quote (') for a double quote (") in NotePad or WordPad.

    Please Login or Register  to view this content.

    You can probably find a VBS script to edit the file but it's probably overkill.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with Quotation Mark.
    By bbmak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2014, 12:29 PM
  2. [SOLVED] Quotation Mark Problem?
    By rcoope02 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-13-2013, 04:56 PM
  3. Replies: 3
    Last Post: 07-29-2013, 08:55 AM
  4. Quotation mark?
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-09-2008, 10:44 AM
  5. Quotation mark (") conversion
    By jgclickfind in forum Excel General
    Replies: 0
    Last Post: 06-04-2007, 10:52 AM
  6. how do I concatenate text that has a quotation mark
    By sparkroms in forum Excel General
    Replies: 2
    Last Post: 07-08-2005, 05:05 PM
  7. joining text and quotation mark
    By yakuzza in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2005, 08:23 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1