inserting "......."

[Neil Greenough]

I have over 3000 rows and in column 3, I have a different word in each
row....

row 1 apple
row 2 banana

etc etc.....

Now, what I need to do is to put speech marks around each word in column 3,
so it reads

row 1 "apple"
row 2 "banana"

Any ideas how I can do this quickly using a function as opposed to inserting
them manually?

 

[Peter Andrews]

Use the function CONCATENATE, less than 5 minutes work should do it.

Peter

 

[Ian]

Sub addquotes()
For r = 1 To 3000
If Cells(r, 3).Value <> "" Then
Cells(r, 3).Value = Chr(34) & Cells(r, 3).Value & Chr(34)
End If
Next r
End Sub

 

[Jean Ruch]

I have over 3000 rows and in column 3, I have a different word in each
row....

row 1 apple
row 2 banana

etc etc.....

Now, what I need to do is to put speech marks around each word in column 3,
so it reads

row 1 "apple"
row 2 "banana"

Any ideas how I can do this quickly using a function as opposed to inserting
them manually?

Hi,

In my (german) Excel with the Formula:
= """"&TEIL((A1);1;LÄNGE(A1))&""""

i.e 4 quotes at the beginning and the end


In the English version it should become to

""""&MID((A1),1,LEN(A1))&""""

Try it.....

regards

Jean

 

[Alan]

With the data in column C, try:
=""""&C1&""""
Drag that down to the end of the range then Copy > Paste Special > Values to
lose the formulas, then cut and paste it back to the original location,
This will definately work, but do it on a copy of your original worksheet in
case of an error causing you to lose the data.
Regards,
Alan.

 

[Neil Greenough]

Thanks all - problem solved

With the data in column C, try:
=""""&C1&""""
Drag that down to the end of the range then Copy > Paste Special > Values
to lose the formulas, then cut and paste it back to the original location,
This will definately work, but do it on a copy of your original worksheet
in case of an error causing you to lose the data.
Regards,
Alan.

 

[David McRitchie]

Hi Neil,
You would be a lot better off doing this with a macro if
this is going to be done on a regular basis. The following
macro would be run after making a selection. You can select
individual cells or entire columns without impairing performance.
The macro will only modify cells with text content.

Sub Enclose_in_quotes()
'modification 2005-10-28 to enclose text values in quotes
'David McRitchie, 2003-08-19 modification of Prefix160
' http://www.mvps.org/dmcritchie/excel/join.htm
Dim cell As Range
On Error Resume Next 'In case no cells in selection
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = """" & cell.Value & """"
Next cell
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

Instructions to install and use a macro can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
would suggest you try using a macro solution, just so you
can see how it works.