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

**lucato** · 2012-02-05, 09:38 AM

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.

**Bob Phillips** · 2012-02-05, 01:19 PM

Code:

Dim vItems As Variant, vItem As Variant
Dim i As Long

    With ActiveCell
    
        vItems = Split(.Value, ",")
        For i = LBound(vItems) To UBound(vItems)
        
            vItems(i) = Trim(vItems(i))
            If InStr(vItems(i), " ") > 0 Then
            
                vItems(i) = Chr(34) & vItems(i) & Chr(34)
            End If
        Next i
        
        .Value = Join(vItems, ",")
    End With

**lucato** · 2012-02-05, 01:29 PM

Hi Bob, thanks for your reply. I'm sorry but I'm newbie with VBA, Macro stuffs, and it seems a VBA, right?
What I remember I hit ALT+F11 and paste the code in VBA/Macro, but I forgot how do I call this code in the cell B1?

Thanks.