Inserting quotes into cells in Excel

[david7777777777]

I've got about 700 rows with 12 columns of data in an Excel 2000 spreadsheet. I need to automatically have Excel insert a single quote mark at the beginning and one at the end of the data in each cell without harming the data in the cell.

I need to export the data into a CSV file, which I've done, but I can't figure out how to wrap each cell's data in single quote marks. Any ideas? Thanks.

Example:

John Smith needs to be 'John Smith'

 

[MarkSweetland]

The quickest way to accomplish the feat would be to import the CSV file into Access and then use the Access Export Wizard to export the table as a Text File(*.csv, *.txt, *.tab, *.asc) which will allow you to specify the Text Qualifier as either a double or single quote, or use no qualifier at all.

I'm sure you can to the same through code, but if it's a one time requirement, Access will do the trick.

Mark

 

[FletchUK]

If you decide to use the code route try this:

1) Create a blank macro

2) Paste this code into the macro:

'
' Insert apostrophe at the begining and end of each cell
' e.g. changes John Smith to 'John Smith'
' created by FletchUK 25/09/2002
'
Dim r As Integer
Dim c As Integer
For r = 1 To 700 'where 1 is 1st row and 700 is last
For c = 1 To 12 'where 1 is 1st column and 12 is last
ActiveSheet.Cells(r, c).Value = Chr(146) & ActiveSheet.Cells(r, c).Value & Chr(146)
Next c
Next r

3) Please note the row starting ActiveSheet.Cells should end with Chr(146) on the same line.......it has been wrapped in the text above. In other words, the next row after ActiveSheet is the one that starts Next c

4) Run the macro

Hope this helps

Fletch