Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How to Add Double Quotes To Fields

Latest post Wed, Feb 2 2011 9:07 PM by Mark. 13 replies.
  • Thu, Feb 28 2008 5:40 PM

    How to Add Double Quotes To Fields

    In a worksheet with text fields, is there a way to surround all fields with double quotes for saving it in csv format later? Some 3rd party applications require "" around each field in import files.

    Example:

    bob | smith | 125 main st | ...     

    need to turn it into

    "bob" | "smith" | "125 main st" |  etc.

     The only solutions I found use MS Works or Access saving options. If Excel  were my only option....

    Thanks.

    • Post Points: 21
  • Thu, Feb 28 2008 6:58 PM In reply to

    Re: How to Add Double Quotes To Fields

    Hi, The array2string macro below should do the job for you. I wrote it a long time ago when I was learning about Excel arrays and have used it constantly since then. By chance, its latest use was to to do exactly what you want. Assuming that your values are in A3:C3, the formula would be ... {=PERSONAL.xls!Array2String(""""&A3:C3&""""," | ")} Please note that array2strng always produces a trailing delimiter. If this causes you a problem then, assuming that the formula is in D3, put the following in E3 ... =mid(D3,1,len(D3)-3) Function Array2String(xArray As Variant, Optional xDelim As String = ",", Optional xNoBlanks As Boolean = False) As String 'Return an array as an (optionally delimited)string, optionally skipping Blanks. 'e.g. =Personal.Xlsb!Array2String({"A","B","C"},"--", False) 'e.g. =IF($A2=$A1,"",Personal.Xls!array2string(IF(($A2:$A31=$A2)*(C2:C31<>0)*(C2:C31<>""),C2:C31,""),CHAR(10),TRUE))} Dim xHold As String Dim xValue As Variant UBound(xArray) For Each xValue In xArray If (Not xNoBlanks Or CStr(xValue) <> "") Then xHold = xHold & CStr(xValue) & xDelim Next Array2String = xHold End Function The function has worked fine for me in Excel 97, 2003 and 2007. Hope it solves your problem, Regards, BrianR
    Filed under:
    • Post Points: 5
  • Thu, Feb 28 2008 7:02 PM In reply to

    Re: How to Add Double Quotes To Fields

    Apologies for the terrible formating of my reply. I'll try to work out what I did wrong and re-post it properly. Regards, BrianR
    • Post Points: 5
  • Thu, Feb 28 2008 7:19 PM In reply to

    Re: How to Add Double Quotes To Fields

    Hi,

    The array2string macro below should do the job for you. I wrote it a long time ago when I was learning about Excel arrays and have used it constantly since then. By chance, its latest use was to to do exactly what you want. Assuming that your values are in A3:C3, the formula would be ...
            {=PERSONAL.xls!Array2String(""""&A3:C3&""""," | ")}
    Please note that array2string always produces a trailing delimiter. If this causes you a problem then, assuming that the formula is in D3, put the following in E3 ...
            =mid(D3,1,len(D3)-3)

    '############################################################################################################################ 

    Function Array2String(xArray As Variant, Optional xDelim As String = ",", Optional xNoBlanks As Boolean = False) As String

    'Return an array as an (optionally delimited)string, optionally skipping Blanks.
    'e.g. =Personal.Xlsb!Array2String({"A","B","C"},"--", False)
    'e.g. =IF($A2=$A1,"",Personal.Xls!array2string(IF(($A2:$A31=$A2)*(C2:C31<>0)*(C2:C31<>""),C2:C31,""),CHAR(10),TRUE))}
       
    Dim xHold As String
    Dim xValue As Variant

    For Each xValue In xArray
        If (Not xNoBlanks Or CStr(xValue) <> "") Then xHold = xHold & CStr(xValue) & xDelim
    Next

    Array2String = xHold

    End Function

    '############################################################################################################################

    The function has worked fine for me in Excel 97, 2003 and 2007. Hope it solves your problem,

    Regards,

    BrianR

    • Post Points: 5
  • Fri, Feb 29 2008 4:13 AM In reply to

    Re: How to Add Double Quotes To Fields

    Or you could try:

    Sub AddQuotes()
        For Each field In ActiveCell.CurrentRegion.Cells
            If field.Value <> "" Then
                field.Value = Chr(34) & field.Value & Chr(34)
            End If
        Next
    End Sub

    Place your cursor in any cell within the range containing your text fields, and run the subroutine. You could also assign a key to the macro to make it easier to run.

    NOTE: This will only work if your range does NOT contain any blank rows or columns.

    Cliff

    • Post Points: 21
  • Wed, Jun 11 2008 1:18 PM In reply to

    • Sitkan
    • Top 500 Contributor
    • Joined on Wed, Jun 11 2008
    • Posts 3
    • Points 47

    Re: How to Add Double Quotes To Fields

     I edited this macro to be:

     

    Sub AddQuote()
        For Each field In ActiveCell.CurrentRegion.Cells
            If field.Value <> "" Then
                field.Value = Chr(34) & field.Value
            End If
        Next
    End Sub

     

    My question to you is, how do I get it to only run on selected cells, rather then the whole spreadsheet?

     

    Dan

    • Post Points: 21
  • Wed, Jun 11 2008 3:18 PM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,415
    • MVP

    Re: How to Add Double Quotes To Fields

    Dan

    You can fix it by using a range variable (myCell for example) and then use that to iterate the 'selection' collection of range objects, like so

     

    Sub AddQuote()
    Dim myCell As Range
        For Each myCell In Selection
            If myCell.Value <> "" Then
                myCell.Value = Chr(34) & myCell.Value
            End If
        Next myCell
    End Sub

     

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    • Post Points: 21
  • Wed, Jan 7 2009 1:26 AM In reply to

    • Ed
    • Top 500 Contributor
    • Joined on Wed, Jan 7 2009
    • Cebu City
    • Posts 3
    • Points 63

    Re: How to Add Double Quotes To Fields

    Nick Hodge:
    Sub AddQuote() Dim myCell As Range For Each myCell In Selection If myCell.Value <> "" Then myCell.Value = Chr(34) & myCell.Value End If Next myCell End Sub

    Hi!

    I appreciate very much for posting the code above. It does insert double-quotes, but it inserts more than 1 pair of double-quote. Instead of "X", I get """X""" when I open the CSV file. Can you help me? Thanks!

    Regards,

    Ed

    Filed under:
    • Post Points: 21
  • Wed, Jan 7 2009 2:47 AM In reply to

    • Ed
    • Top 500 Contributor
    • Joined on Wed, Jan 7 2009
    • Cebu City
    • Posts 3
    • Points 63

    Re: How to Add Double Quotes To Fields

    Hi!

    I just found out that Excel treats double-quote characters that way: by adding additional double-quotes in the CSV file. Any suggestions on how to remove the extra double-quotes from the CSV file?

     

    Regards,

    Ed

    • Post Points: 21
  • Wed, Jan 7 2009 1:56 PM In reply to

    • Sitkan
    • Top 500 Contributor
    • Joined on Wed, Jun 11 2008
    • Posts 3
    • Points 47

    Re: How to Add Double Quotes To Fields

    Ed,

    What program are you using to open your CSV file?  If its Notepad, just simply do Ctrl+H and put in "" to the find and " to the replace.  That should get rid of your double double quotes and leave you with one double quote. 

     

    ~Dan

    • Post Points: 21
  • Wed, Jan 7 2009 11:11 PM In reply to

    • Ed
    • Top 500 Contributor
    • Joined on Wed, Jan 7 2009
    • Cebu City
    • Posts 3
    • Points 63

    Re: How to Add Double Quotes To Fields

    Dan,

     

    Thanks for the tip. A friend told me exactly the same thing. It works just fine. Any reasons why MS Excel treats double-quotes that way?

     

    Regards,

    Ed

    • Post Points: 21
  • Thu, Jan 8 2009 4:31 PM In reply to

    • Sitkan
    • Top 500 Contributor
    • Joined on Wed, Jun 11 2008
    • Posts 3
    • Points 47

    Re: How to Add Double Quotes To Fields

    Ed,

    I'm not knowledgable enough to answer that question.  Perhaps one of the excel guru's cruising the forums can answer that for you :)

    ~Dan

    • Post Points: 5
  • Fri, May 21 2010 3:05 PM In reply to

    • donald1
    • Not Ranked
    • Joined on Fri, May 21 2010
    • Posts 1
    • Points 5

    Re: How to Add Double Quotes To Fields

    hello every one . iam new in this forum this is really a good  forum i found it when i was seeking for an excle problem and now i am a member of thiis.

     

     

    regards.

    Normal 0 false false false MicrosoftInternetExplorer4 &lt;!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --&gt; /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;}

    Disaster Recovery and Business Continuity Plan

     

    • Post Points: 5
  • Wed, Feb 2 2011 9:07 PM In reply to

    • Mark
    • Not Ranked
    • Joined on Wed, Feb 2 2011
    • Stockport, UK
    • Posts 1
    • Points 5

    Re: How to Add Double Quotes To Fields

    I  went through this hoop yesterday with having to edit csv or tabbed files.

    Also had problem with getting unwanted columns I'd hidden (containing elements used to compute the main strings)

     

    In the end, I found that output to "filname.xyz" in format .prn file dows the job. e.g. xyz = bat (- if your fileserver permits creation of .bat files ;-) )

    in .prn format, CONCATENATE(CHAR(34), "QWERTY", CHAR(34))  gives  exactly "QWERTY" and hidden columns aren't output as it isn't trying to reproduce the design file in a different format.

     

    Another tip-pair from my recent experience (generating a bunch of xcopy /e SRC "path"   rows in a .bat file

    1. use tab separation but

    1a ....if you are pasting (lines from the file) into DOS window, force a trailing space inside the double quotes as otherwise TAB-completion will kick-in.

    e.g. generate   xcopy /e SRC "\\dt-ournet-03421\c$\path "{TAB}

     

    - Two years and a month too late I'm afraid but it might help new readers :-)

     

    • Post Points: 5
Page 1 of 1 (14 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.