How Do I Quickly Enclose String in Quotes?

Can you give me a list of all the product information that we hold for these products?

As a Business Analyst, that is a fairy simple question to answer – just a matter of a couple of joins and a where-clause.

What could make it annoying and unnecessarily time-consuming, however, is the necessity to use text IDs (strings) in your where-clause. Then all of them will need to be encircled in single quotes (‘ ‘) if the query is to run.

As you can imagine, those could be quite annoying to type in for all the 300+ IDs that you need to retrieve.

A time-saving technique in such cases would be to do some pre-query prep by running the code below on your list. This will add the single quotes and the commas needed between the IDs.

Sub EncloseinQuotes()
Dim Cell As Range
Dim IP As String
'written by Angelina Teneva, March 2017
With ActiveSheet
For Each Cell In Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
IP = Cell.Value
Cell.Value = "''" & IP & "',"
Next Cell
End With
End Sub

Then, all you need to do is a concatenation, which will render the IDs easy to paste in your SQL query. One handy technique to do so is to:

  1. move the first value to the adjacent column
  2. use the following formula

Happy querying!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.