Link to home
Create AccountLog in
Avatar of jhoekman
jhoekmanFlag for United States of America

asked on

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

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.  

Ideas?
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jhoekman

ASKER

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!
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) & """"

Brad

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
Next
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!