Put Entire Cell Contents Within Double Quotes (")

[Kickingkangaroos]

Hi All,

I need to create a daily CSV file for upload into a CMS.

However, the format I get my report in is standard .xls - as such, I need to ensure the content is suitable for a CSV.

One of the columns contains text, which (helpfully!) includes commas here and there, as such I need to wrap each of the cells in this column (B) in double quotes (e.g. from Train, Red, with blue, yellow and white trim to "Train, Red, with blue, yellow and white trim") to ensure the CSV uploads correctly.

Is there a quick/easy way to do this for multiple rows?

Many thanks,

Andy

 

[tigeravatar]

Andy,

You can use the following to wrap quotation marks around each cell's value in column B:

Sub tgr()
    
    Dim arrB As Variant: arrB = Intersect(ActiveSheet.UsedRange, [B:B]).Value
    Dim r As Long
    For r = 1 To UBound(arrB, 1)
        arrB(r, 1) = """" & arrB(r, 1) & """"
    Next r
    Intersect(ActiveSheet.UsedRange, [B:B]).Value = arrB
    
End Sub

Hope that helps,
~tigeravatar

 

[Kickingkangaroos]

Hi ~tigeravatar,

That's absolutely great - and works perfectly.

Thank you very much indeed. Have a great weekend.

Andy