Inserting quote marks in populated cells

[david7777777777]

How do you have Excel 2000 automatically insert single quote marks around existing data in cells? I've got about 700 rows with 12 columns of data. Thanks.

 

[xlhelp]

Dunno why you would need it, but here it is.

Highlight your whole range and run the following macro. Just in case you don't know what to do:
Go to Tools, Macro,Visual Basic Editor, Insert, Module
and copy the macro in the right hand pane.
Since Excel treats the leading single quote to denote start of text, I have included two single quotes in fornt of the vlaues.

Sub quotes()
Dim curntcell As Object
For Each curntcell In Selection
curntcell.Value = "'" & "'" & curntcell.Value & "'"
Next curntcell

End Sub

 

[GlennUK]

You could use another sheet and put formulae in to add the single quotes, and then afterward select the results and do copy/paste values to generate the result you want.

For example, if your original values are in Sheet1 of a workbook, then put this formula in cell A1 of another sheet, and copy it for 700 rows and 12 columns ...
=IF(Sheet1!A1<>&quot;&quot;,&quot;'&quot;&Sheet1!A1&&quot;'&quot;,&quot;&quot
and then follow the rest of the suggestions.

Hope that helps, Glenn.

 

[david7777777777]

Thanks. So the & symbol acts as a value &quot;wrapper&quot; in VB?

 

[david7777777777]

xlhelp,
Thanks, that worked perfectly. I'm an ex-instructor myself. I'm doing ASP development now and still there's plenty for me to learn. I'm wondering about the syntax of this line:

curntcell.Value = &quot;'&quot; & &quot;'&quot; & curntcell.Value & &quot;'&quot;

I understand how and why it works, but I don't understand what the purpose of the &quot;'&quot; right after the = sign. What is that single quote for?

 

[xlhelp]

In Excel when you put a single quote in any cell, it tells Excel that you are starting to input text regardless of whether it is number or text itself. As such, the leading single quote will get ommited when printing. That's the reason for putting the single quote twice.

 

[FletchUK]

Here's yet another option! Instead of having to use two single quotes (&quot;'&quot; & &quot;'&quot you can use the ascii key code for a single quote. 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

Fletch