Adding Quotes ("") Before and After Keyword in Cell

jhoekman used Ask the Experts™
Hey experts!  

I am trying to take a keyword phrase that I have in one cell and automatically add quotes to before and after the phrase in a new cell.  So, what would start as ' keyword example ' in one cell would become ' "keyword example" ' in another.  

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
You need to double up on the double quotes:
="""" & A2 & """"           'That's four double quotes in a row, each time. The starting & ending double quotes, plus two for each one you need in the result



Two correct answers in 3 minutes... you Excel gurus are amazing!  

Funny thing, I had tried the almost correct code, just with 3 " in a row, rather than 4.  

Thanks a bunch!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Or you may find it less confusing to use the ASCII character code:
=CHAR(34) & A2 & CHAR(34)

I don't know your ultimate aim, but you might be trying to build a SQL Query. If so, I have a function that lets you build a result string like: "Cat", "Dog", "Hoekman"
You call it with a formula like:
="""" & SQLbuilder(IV2:IV4,"",A2:A4,""", """,FALSE) & """"             'Concatenate the keywords in A2:A4.  Cells IV2:IV4 are assumed to be blank.

The function actually lets you look for a match in a different column. For example, if you only want to concatenate when column B contains TRUE, the formula would be:
="""" & SQLbuilder(B2:B4,TRUE,A2:A4,""", """,FALSE) & """"


Function SQLbuilder(vTestArray As Variant, vMatch As Variant, vReturnArray As Variant, sSeparator As String, Optional bCaseSensitive As Boolean = False) As String
'Builds a concatentated string of elements from vReturnArray that correspond to cases where vTestArray elements match vMatch
'Elements in the concatenation are separated by sSeparator
'If bCaseSensitive is true, then the match is case sensitive
Dim i As Long
Dim v As Variant
Dim s As String
For Each v In vTestArray
    i = i + 1
    If bCaseSensitive Then
        If v = vMatch Then s = s & sSeparator & vReturnArray(i)
    ElseIf UCase(v) = UCase(vMatch) Then
        s = s & sSeparator & vReturnArray(i)
    End If
If s <> "" Then SQLbuilder = Mid(s, Len(sSeparator) + 1)
End Function

Open in new window


HA!  SQL... you overestimate my abilities as a lowly marketer!  :)

Thanks for the above and beyond help, though, Brad.  You da man!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial