Sandwich text between single quotes, macro

C

Captain Smith

Active Member
Joined
Feb 28, 2003
Messages
324
Hello. I wrote the code below to sandwich data in a cell between single quotes. I was forced to used two single quotes in the first concanate portion because as you know excel treats one single quote at the beginning of a text field to be non-formula. Do you see any issues with my code adding two single quotes at the beginning? It seems to work, but I don't want to be surprised one day when it might not. Thank you.
In case it is hard to read, the string part of my code is double quote, single quote, single quote, double quote, &cell.Value&, double quote, single quote, double quote.

Sub AddSingleQuote()
Dim n As String
Dim cell As Range
For Each cell In Selection
cell.Value = "''" & cell.Value & "'"
Next
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Couldn't you just do cell.value = cell.formula? It should then format it as text and you no longer need the single quote
 
Upvote 0
I actually need the text wrapped between two single quotes, so CAT becomes 'CAT'
Reason is that I need to export it to another application in this format.
Thanks.
 
Upvote 0
In that case what you have done looks OK but the starting one looks like a triple set of double quotes instead of double single double
 
Upvote 0
try
Code: 
Sub AddSingleQuote()
Dim x As String
With Selection
    x = .Address
    .Value = Evaluate("if(" & x & "<>"""",""'""&" & x & "&""'"","""")")
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
414
AndyEd
A
P
  • Solved
single-formula combining of multiple dynamic arrays (VSTACK?)
Replies
4
Views
839
Plod518
P
C
  • Solved
Need to modify TextJoin code to populate not just the top row cell, but all the way down
2
Replies
12
Views
543
Peter_SSs
Peter_SSs
S
  • Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
655
NdNoviceHlp
NdNoviceHlp
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
322
Clif McIrvin
C

Forum statistics

Threads
1,215,135
Messages
6,123,238
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top