• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

put quotes around a field

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
0
onyourmark
Asked:
onyourmark
  • 5
  • 5
  • 4
  • +1
2 Solutions
 
Brian GeeCommented:
An apostrophe in the beginning of the cell will do this.

'"test"
0
 
Brian GeeCommented:
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:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26966853.html
0
 
rspahitzCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
onyourmarkAuthor 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.
0
 
rspahitzCommented:
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.)
0
 
onyourmarkAuthor Commented:
i want to put quotes around every field. is it hard to do that in vba?
Thanks
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
onyourmarkAuthor Commented:
Hi. That put triple quotes on the fields but thanks anyway.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
rspahitzCommented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Aha! I missed the export part.

If the code above doesn't work, try this code. I've used it many times to do the deed.

The default Excel comma separated values (CSV) export format only places quotes around fields that contain a quote, a comma, or one of a few special characters. Some programs require that all fields be enclosed in quotes so a custom export routine is required. The routine below exports a worksheet as a CSV file where every field is enclosed in quotes, or only text fields or numeric values with commas are enclosed in double quotes. Fields are exported exactly as they are formatted.

There is one main routine, ExportRangeAsAllQuotedCSV, and two wrapper routines that call ExportRangeAsAllQuotedCSV. The wrapper routines are designed to be called as macros. The wrapper routine ExportRangeAsAllQuotedCSVDefault calls ExportRangeAsAllQuotedCSV with a default set of parameters. The wrapper routine ExportRangeAsAllQuotedCSVQuery queries for the export file path parameter, and then calls ExportRangeAsAllQuotedCSV with the results. The parameters to ExportRangeAsAllQuotedCSV are documented in the routine code.

[Begin Code Segment]

Public Sub ExportRangeAsQuotedCSVDefault()

' Call the routine ExportRangeAsQuotedCSV using a predefined set of parameter
' values.

   ExportRangeAsQuotedCSV "C:\Full\Path\To\FileName.csv", ActiveSheet.UsedRange
   
End Sub

Public Sub ExportRangeAsQuotedCSVQuery()

' Call the routine ExportRangeAsQuotedCSV after querying for the required
' parameter values.

   Dim FilePath As Variant
   Dim FileName As String
   
   FileName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
   
   FilePath = Application.GetSaveAsFilename(FileName & ".csv", "Comma Seperated Values File (*.csv), *.csv")
   If VarType(FilePath) = vbBoolean Then Exit Sub
   ExportRangeAsQuotedCSV FilePath, ActiveSheet.UsedRange

End Sub

Public Sub ExportRangeAsQuotedCSV( _
      ByVal FilePath As String, _
      Optional ByVal SourceRange As Range, _
      Optional ByVal QuoteTextOnly As Boolean _
   )

' Export the specified range as a CSV file with all fields enclosed in double
' quotes. Each field is formatted exactly as it is formatted in the cell.
'
' Syntax
'
' ExportRangeAsAllQuotedCSV(FilePath, [SourceRange], [QuoteTextOnly])
'
' FilePath - The fill file path of the new export file.
'
' SourceRange - The range to export. Optional. If omitted then the entire used
'   range of the active worksheet is exported.
'
' QuoteTextOnly - Pass True to only quote text fields, pass False to quote all
'   fields. Optional. If omitted then False is assumed.

   Dim Row As Range
   Dim Values() As String
   Dim Column As Long
   Dim FileData As String
   Dim FileNumber As Long
   
   If SourceRange Is Nothing Then Set SourceRange = ActiveSheet.UsedRange
   ReDim Values(1 To SourceRange.Columns.Count)
   For Each Row In SourceRange.Rows
      For Column = 1 To UBound(Values)
         Values(Column) = Row.Columns(Column).Text
         If VarType(Row.Columns(Column)) = vbString Or Not QuoteTextOnly Or InStr(Values(Column), ",") > 0 Then
            Values(Column) = """" & Replace(Values(Column), """", """""") & """"
         End If
      Next Column
      FileData = FileData & Replace(Join(Values, vbTab), vbTab, ",") & vbCrLf
   Next Row
   If Len(Dir(FilePath)) > 0 Then Kill FilePath
   FileNumber = FreeFile
   Open FilePath For Binary Access Read Write As FileNumber
   Put FileNumber, , FileData
   Close FileNumber

End Sub

[End Code Segment]

Kevin
0
 
onyourmarkAuthor Commented:
Here are the file and the resulting file. quotetest.csv quotetest2.csv
0
 
rspahitzCommented:
Based on the original file you supplied, you obviously didn't run my code.
So what are you trying to show us now?
0
 
onyourmarkAuthor Commented:
this is the result of the first macro posted.
0
 
rspahitzCommented:
Go to your original file (posted in your question)
then add my code from question # 35488481
Run it and your should get results as I stated.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now