We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

put quotes around a field

onyourmark
onyourmark asked
on
Medium Priority
334 Views
Last Modified: 2012-05-11
I am trying to get my fields to be quoted in Excel. I tried using
=CHAR(34)&C2&CHAR(34)
and also tried
=""""&C2&""""

But both of these give triple quotes when I look at these in a simple text editior. I am attaching the sheet as an example.
quotetest.xls
Comment
Watch Question

Top Expert 2011

Commented:
An apostrophe in the beginning of the cell will do this.

'"test"
Top Expert 2011

Commented:
Your sheet reminded me of another person here who asked a similar question and had data that looked similar to yours. To see the solutions offered to him (maybe some of these apply to your situation as well), see here:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26966853.html

Commented:
Works fine for me when I copy (Ctrl+C) cell F4 and paste (Ctrl+V) into Notepad.

Are you trying to export the data?  If you do that, Excel may add extra quotes to make it CSV-compatible.

Author

Commented:
Yes I am trying to export the data. I would be happy if Excel added quotes before I use the formulas above but it does not at that point. Maybe it is doing that after I add the quotes. I want to have just a single set of quotes when saving it as a csv file and then opening it in notepad.
Thanks.

Commented:
I think the problem is that without the quotes, Excel realizes that they are not needed.
With the quotes, Excel realizes that (in order to retain the original quoted data) it needs to put extra quotes around the items.

There may be a setting to tell Excel to force quotes around items when you export them, but I know of nothing like that.

However, it would be easy enough to do in VBA if you'd like to try that (although defining the parts to export will be the difficulty.)

Author

Commented:
i want to put quotes around every field. is it hard to do that in vba?
Thanks
CERTIFIED EXPERT
Top Expert 2008

Commented:
Use this macro to place quotes around the text in the selected cells:

Public Sub QuoteText()

    Dim Cell As Range
    For Each Cell In Selection
        Cell.Value = """" & Cell.Value & """"
    Next Cell

End Sub

Kevin

Author

Commented:
Hi. That put triple quotes on the fields but thanks anyway.
CERTIFIED EXPERT
Top Expert 2008

Commented:
What the heck are you running over there? ;-)

The code puts two double quotes around whatever text is in the cell. If there is already a quote then that quote is included and you will get more quotes than you want. Once the data is quoted, your formulas will also produce extra quotes because they will now be quoting already quoted text.

Try putting in some plain text strings in some unused cells and running the macro on them. You will see the desired results.

Kevin
CERTIFIED EXPERT
Top Expert 2008

Commented:
I ran the macro on all the text fields (and deleted the formulas since they are not needed any more) and produced the desired results.

See the attached workbook.

Kevin
quotetest.xls

Commented:
A simple way in VBA would be something like this:

 
Private Sub CreateFile()
    On Error GoTo CreateFile_Err
      
    Dim strPath As String
    Dim iFileNumber As Integer
    Dim iRow As Integer
    Dim iColumn As Integer
    
    strPath = Environ("Temp")
    iFileNumber = FreeFile()
    Open strPath & "\MyFile.CSV" For Output As #iFileNumber
    iRow = 3
    Do Until Cells(iRow, 1).Value = ""
      For iColumn = 1 To 5
        If iColumn > 1 Then
          Print #iFileNumber, ",";
        End If
        Print #iFileNumber, Chr(34) & Replace(Cells(iRow, iColumn).Value, vbLf, " *** ") & Chr(34);
      Next iColumn
      Print #iFileNumber, ""
      iRow = iRow + 1
    Loop
    
CreateFile_Exit:
    Close #iFileNumber
    
    MsgBox "File saved as " & strPath & "\MyFile.CSV"
    Exit Sub
  
CreateFile_Err:
    MsgBox "error found during save: " & Err.Description
    Resume CreateFile_Exit
End Sub

Open in new window


The only problem is that I see your data in column E containing multiple lines.  I "merged" these into a single line where each new line is replaced by "***"

I'll have to see how the CSV standard supports multiple lines, but otherwise this code should handle it for you (although you may want to change the path and the file name (plus the number of columns if more than 5.)
Also, I am assuming that your data does NOT contain any quotes, otherwise we'd have to double-the quotes as you saw with Excel's export.
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Here are the file and the resulting file. quotetest.csv quotetest2.csv

Commented:
Based on the original file you supplied, you obviously didn't run my code.
So what are you trying to show us now?

Author

Commented:
this is the result of the first macro posted.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.