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.

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

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

 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

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