How to change composed keywords separated by comma into a cell?

[lucato]

Hi folks, nice to meet you all.

Lets suppose I have the cell A1 with 100 words or more separated by comma, but the words can be composed word or double words such as "jump up", "Old age","sunny day", or more "holding a face", but I want to find these composed words or double/triple+ words with space that are between commas, and put them between double-quotes. So, as example let's suppose my cell A1 was as:

[A1] word1, jump up, word3, word4, Old age, word6, Sunny day, holding a face,...word100

Want the result as

[A1] word1, "jump up", word3, word4, "Old age", word6, "Sunny day", "holding a face",...word100

It can be a formula on B1 or a trick by using find&replace.

Thanks in advance.

 

[Rick Rothstein]

I don't doubt that there is some clever formula-maker out there who will figure out a formula solution for you, but just cannot see anyway to do it. How about a UDF (user defined function) instead...

Function MultiQuote(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ", ")
  For X = 0 To UBound(Parts)
    Parts(X) = Trim(Parts(X))
    If InStr(Parts(X), " ") Then Parts(X) = """" & Parts(X) & """"
  Next
  MultiQuote = Join(Parts, ", ")
End Function

If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the UDF, go back to the worksheet and put this formula in B1...

=MultiQuote(A1)

 

[lucato]

Wow Rick, thanks for the solution very well explained how to use and a quick reply. I appreciated that.
Worked like a charm.
Once again, thanks a lot and have a nice Sunday.