Macro to add prefix and suffix to cell

[heavy_metal_rckr]

Here is the situation...

I have a set of numbers (Excel 2010), but for reasons I have no control over, they are to be formatted as text. I need to add an ' to the beginning and a ', to the end of each, so that I can copy and paste the list into a SQL query and they will be seen as text and not numbers.

Sample: " 999999999 " becomes " '999999999', " (without the spaces of course)

I tried this:

Sub Add_Apostrophe()

Dim r As Range

With Selection

For Each r In Selection
r.Value = "'" & r.Value & "',"
Next

End With

End Sub


However, when I copy and paste the list into SQL Developer, it only sees the ending and not the beginning. I have also tried a custom format, "'"@"'," and that does not work either. Ideas??

[heavy_metal_rckr]

Nevermind all... I figured it out. END THREAD

[irascari]

Did you try adding other ' in the code?

For Each r In Selection 
r.Value = "''" & r.Value & "'," 
Next

[irascari]

so, nevermind with my answer, hahaha