adding quotes to existing Data

[mrbiggles]

Hello
This is probably an easy thing to do, I have an excel spread sheet with about 1000 rows. It needs to be imported into an SQL database, I have found out that 2 of the columns need to have quotations around the data.

this is the current data

"this is how I need it to be"

I have tried using ctrl + shift keys while dragging the quote but it just seems to overite the data.

I have tried some formulas but I am not at all experienced in this, and when I try and modify it for my purpose it stuffs it up.

I am surprised I would need a formula to do something so simple ( I am sure I am missing something so simple). It does not help that I do not know the terminology and consequently do not know what to search on without getting hundreds of irrelevant items.

My version of Excel is 2002 sp 3
I use windows XP

Regards Adam

 

[timwangTM]

Suppose your first data in cell A1, then compose formula in B2 as following to quote your data: =""""&A1&"""".

It works in my excle 2000.

 

[mrbiggles]

Hello
I tried this, but it just copies the A1 data over the B1 data, all of the data is different, here is a sample.

DIGITAL CAM,3.34MP,5XDIG ZOOM,1.5IN LCD 16MB INTERNAL MEMORY
LABTEC WEBCAM USB - RETAIL PACK
USB CAMERA/300K PIXL/USB PWR/BMP/JPEG/AVI SNPSHT/640 X 480

The quotes are required so that SQL will not mix up the data while its being imported ie: treats it as one entry.

While I cannot write the formula, in plain english it would do this

Insert Quotes into cell.
append to existing cell data.

I found this formula

Sub AddSingleQuote()
Dim currentcell
For Each currentcell In Selection
'Prevents inserting apostrophes in blank cells.
If currentcell.Formula <> "" And Len(currentcell.Value) = 7 Then
currentcell.Formula = """" & currentcell.Formula & """"
End If
Next
End Sub

How would I modify this to suit what I need to do.
Regards Adam
Excel 2002 SP3
Windows XP

 

[PA HS Teacher]

Tim's solution works for me. I don't understand why it does not work for you or why a VBA solution would be any better?

 

[mrbiggles]

Tim's solution works for me. I don't understand why it does not work for you or why a VBA solution would be any better?

I think it is my knowledge that is the problem, ive probably done something wrong, I will try the original solution again.
Regards Adam.

 

[parry]

Yes you can certainly use a formula as per Tims suggestion. You then copy the results and paste special|values back over the original data.

The problem with the code you posted is that it only works if the length of the cell is 7 characters so that part needs to be removed. Select the cells (dont select the whole column or it will take ages to run) and run the macro...

Sub AddSingleQuote()
Dim currentcell
For Each currentcell In Selection

'Prevents inserting apostrophes in blank cells.
If currentcell.Formula <> "" Then
currentcell.Formula = """" & currentcell.Formula & """"
End If
Next
End Sub

 

[mrbiggles]

Hello
Found an answer
I used the following

=""""&TEXT(D4,"string")&""""

it worked for me, I must have been doing something wrong with the other solution.
Thanks for the help